MVSFORUMS.com

[tommy123]

Hai,

I am trying to work on different querries as below

1.I am using the following query in QMF to know my age :

SELECT (CURRENT DATE - '1976-05-27') "YEAR-MONTH-DAYS" FROM SYSIBM.SYSDUMMY1 and i get the display

YEAR-MONTH-DAYS
---------------
281129


When i try to add 10 days more to my age using this query i am getting error

SELECT (CURRENT DATE - '1976-05-27' + 10 DAYS )
"YEAR-MONTH-DAYS" FROM SYSIBM.SYSDUMMY1


"An arithmetic expression with a datetime value is invalid."


2.I am trying to find the difference in birthdates of two persons by giving this query

SELECT ('1976-05-27' - '1978-1-18') "YEAR-MONTH-DAYS" FROM SYSIBM.SYSDUMMY1

I am getting the error :

"Argument '2' of scalar function '-' is invalid."

3.I am trying to find elder of the two persons by using this query

SELECT MAX('1976-05-27','1978-1-18') FROM SYSIBM.SYSDUMMY1

I am getting the display as

COL1
----------
1978-1-18

where my intention is to get the person who has born earlier ie elder '1976-05-27'

4.I want to know the date ,10 days before my birthday for which i am using the query

SELECT ('1976-05-27' - 10 DAYS) FROM SYSIBM.SYSDUMMY1

I am getting error "Argument '2' of scalar function '-' is invalid."

5.I want to know how much age i am in YYMMDD format if i try to subtract 3 months from my birthday .


Can anyone tell me how to correct my querries / how to code them properly ?

[kolusu]

Tommy123,

[NASCAR9]

Kolusu,
I ran a couple of the above queries and I don't understand the result, can you help?
SELECT CURRENT DATE - (DATE('1976-05-27') + 10 DAYS)
FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+

---------+---------+---------+---------+---------+---------+
281120.
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+

SELECT DATE('1978-05-27') - DATE('1976-1-18')
FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+

---------+---------+---------+---------+---------+---------+
20409.
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

[kolusu]

Nascar9,

An extract from the link provided in the earlier post.

Subtracting dates: The result of subtracting one date (DATE2) from another (DATE1) is a date duration that specifies the number of years, months, and days between the two dates. The data type of the result is DECIMAL(8,0). If DATE1 is greater than or equal to DATE2, DATE2 is subtracted from DATE1. If DATE1 is less than DATE2, however, DATE1 is subtracted from DATE2, and the sign of the result is made negative.

Check this link for a better understanding of Date arithmetic

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/2.20.9.1?SHELF=&DT=20010718164132&CASE=

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu