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
how to use year function in db2 - date difference
Moderators: Kalicharan, Moderator Group
how to use year function in db2 - date difference
waseem......
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
are you receiving a negative sql code?waseem wrote:its (sic) not working..
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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
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
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......
DB2 DATE DIFFERENCE SUBTRACT DATES
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.
Declare WS-DATE-DIFF as follows
Let me know, if you have any query on this.
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
Code: Select all
01 WS-DATE-DIFF PIC S9(06) COMP.
Let me know, if you have any query on this.
Regards,
Krishna
Facebook --> http://www.facebook.com/mainframegurukul
Connect with RAMESH KRISHNA REDDY --> http://www.linkedin.com/in/rameshkrishnareddy
Twitter --> https://twitter.com/mainframegurkul
http://www.mainframetutorials.com
Krishna
Facebook --> http://www.facebook.com/mainframegurukul
Connect with RAMESH KRISHNA REDDY --> http://www.linkedin.com/in/rameshkrishnareddy
Twitter --> https://twitter.com/mainframegurkul
http://www.mainframetutorials.com
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page