how to use year function in db2 - date difference

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
waseem
Member
Posts: 4
Joined: Tue Nov 09, 2010 8:04 pm

how to use year function in db2 - date difference

Post by waseem » 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
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
waseem......

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » 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.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

waseem
Member
Posts: 4
Joined: Tue Nov 09, 2010 8:04 pm

Post by waseem » 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
waseem......

waseem
Member
Posts: 4
Joined: Tue Nov 09, 2010 8:04 pm

Post by waseem » 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!!!
waseem......

User avatar
Krishna
Site Admin
Posts: 1052
Joined: Fri Jan 27, 2006 7:50 am

DB2 DATE DIFFERENCE SUBTRACT DATES

Post by Krishna » Mon Nov 15, 2010 11:48 am

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.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others