Page 1 of 1

how to use year function in db2 - date difference

Posted: Tue Nov 09, 2010 8:11 pm
by waseem
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
exec sql
select year( current_date - :ws-date )
into :ws-year
from sysibm.sysdummy1
end-exec

*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
by dbzTHEdinosauer
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
by waseem
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
by waseem
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
by Krishna
Hi Waseem,

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


EXEC SQL                           
     SELECT DAYS (:WS-DATE-S) - 
            DAYS (:WS-DATE-E) 
       INTO :WS-DATE-DIFF          
       FROM SYSIBM.SYSDUMMY1       
END-EXEC

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.