Page 1 of 1
how to use year function in db2 - date difference
Posted: Tue Nov 09, 2010 8:11 pm
in my program i have to calculate the no. of years between two dates ie.,current date and a another date stored in a working storage variable
but wn iam using
select year( current_date - :ws-date )
*01 ws-date pic x(10) value '1990-10-13'
01 ws-year pic x(10).
its not working...so someone suggest me the correct data types and format for the date variables and syntax for the above query
Posted: Tue Nov 09, 2010 9:03 pm
waseem wrote:its (sic) not working..
are you receiving a negative sql code?
what results are you receiving?
have you tried this with spufi?
why don't you look up the function year and see what datatype it returns?
how could a Pic x(10) refer to a year?
and you may want to qualify the host variable ws-date with a date cast,
as in date(:ws-date)
by the way, 'it's not working' is a useless thing to say.
you put as little thought into defining your resultant host-variable as you did in describing the results of your sql.
Posted: Wed Nov 10, 2010 9:20 am
yes iam getting an unsuccesful binding error....with spufi the same query is working...
even in cobol-db2
select current_date into :ws-date from sysibm.sysdummy1
is working but
select year(current_date) into :ws-date from sysibm.sysdummy1
both for ws-date pic x(10) and pic 9(10)
is not giving any error but iam getting nothing into ws-date when iam displaying ws-date and blank is getting displayed
Posted: Sat Nov 13, 2010 6:44 pm
i tried the functions like count() year() in cobol-db2 they are not working
i think it might be that we cannot use such functions of sql in cobol-db2.....
and its not a foolish thing that i did'nt use the proper datatypes mr.dbzTHEdinosauer!!!
DB2 DATE DIFFERENCE SUBTRACT DATES
Posted: Mon Nov 15, 2010 11:48 am
Here is the code to find number of days between two days using db2 sql.
You can use this data to find no of years between two dates.
Code: Select all
SELECT DAYS (:WS-DATE-S) -
Declare WS-DATE-DIFF as follows
Code: Select all
01 WS-DATE-DIFF PIC S9(06) COMP.
Let me know, if you have any query on this.