Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Get the difference in date

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
prasanthi mathireddi

New User


Joined: 19 Jun 2008
Posts: 2
Location: chennai

PostPosted: Thu Jun 19, 2008 11:27 pm    Post subject: Get the difference in date
Reply with quote

Hi,

I need to find out the difference between two dates(YYYY-MM-DD) in cobol-db2 program.
Output should also needed in Date format(YYYY-MM-DD).

EX: If input dates are 2008-06-20 and 2005-05-01 which i am getting it from query , i need to find out the difference between two dates as 0001-01-19.

Thanks,
Prasaha
Back to top
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Jun 20, 2008 12:36 am    Post subject:
Reply with quote

How the difference between 2008-06-20 and 2005-05-01 will be
0001-01-19?

How did arrive this result?

To find out the difference use the below query.

SELECT DATE(DATE('2008-06-20') - DATE('2005-05-01'))
FROM SYSIBM.SYSDUMMY1;;

I don't understand what you are going to achieve with this (Difference between 2 dates in date format).
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Fri Jun 20, 2008 11:09 am    Post subject: Reply to: Get the difference in date
Reply with quote

Well as Suresh has pointed out, the result 0001-01-19 from the difference between 2008-06-20 and 2005-05-01 seems illogical.

As far finding the difference between 2 dates(as an integer) go, the std SQL would be,
Code:

SELECT DAYS('2008-06-20') - DAYS('2005-05-01')
FROM SYSIBM.SYSDUMMY1;


But you need to be sure what you need!

regards,
asci(i)
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Jun 20, 2008 7:43 pm    Post subject:
Reply with quote

I always point people to this article when they start to ask how to do date stuff with DB2. Does no one search Google for "db2 dates" before posting here?

http://www.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html
Back to top
View user's profile Send private message
prasanthi mathireddi

New User


Joined: 19 Jun 2008
Posts: 2
Location: chennai

PostPosted: Fri Jun 20, 2008 10:54 pm    Post subject: Reply to: Get the difference in date
Reply with quote

Thanks Suresh. Your query worked well but with little modifiation. i changed the query as below.

SELECT CHAR(DATE('2008-06-20') - DATE('2005-05-01'))
INTO :WS-OUT-DATE
FROM SYSIBM.SYSDUMMY1;

I got result WS-OUT-DATE as 00030119

Later i manipulated WS-OUT-DATE to reqired date format.

When i tried the below query

SELECT DATE(DATE('2008-06-20') - DATE('2005-05-01'))
FROM SYSIBM.SYSDUMMY1;

i didn't get correct result.

Thanks a lot for all who have gave reply for my post.
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Sat Jun 21, 2008 6:28 am    Post subject:
Reply with quote

Quote:
SELECT DATE(DATE('2008-06-20') - DATE('2005-05-01'))
FROM SYSIBM.SYSDUMMY1;

What result did you get when you executed this query?
What is the declaration of the variable : WS-OUT-DATE?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us