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
 

 

Difference between two db2 dates in years

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Tue Apr 07, 2009 10:02 am    Post subject: Difference between two db2 dates in years
Reply with quote

I wanted to find the difference between two dates such that the result is in years

For Eg: '2001-12-31' - '2000-01-01' ~ 2yrs

When i use the query

SELECT YEAR('2001-12-31') - YEAR('2000-01-01')
FROM SYSIBM.SYSDUMMY1

I get the difference as 1 yr as this subtracts 2001-2000 = 1 yr

Is there any way that i can get the approximate difference in years between two dates?
Back to top
View user's profile Send private message

kregen

New User


Joined: 16 Mar 2006
Posts: 18

PostPosted: Tue Apr 07, 2009 6:15 pm    Post subject: Reply to: Difference between two db2 dates in years
Reply with quote

i don't understand what you exactly mean!

when you subtract two date you get a result like this



Code:
SELECT DATE('2010-05-31') - DATE('2000-07-01')
FROM SYSIBM.SYSDUMMY1                         
;         
                                     
091030,



yymmdd =

first two digits = the difference of year's
second tow digits = the difference of month's
last tow digits = the difference of day's

i hope it's help!

cu
kregen
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Apr 07, 2009 9:43 pm    Post subject:
Reply with quote

Hello,

Notice that the original sql used "year" and the suggestion uses "date".

Quite a difference. . . .
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Tue Apr 07, 2009 10:33 pm    Post subject: Reply to: Difference between two db2 dates in years
Reply with quote

first define the requirement, and then let' s look at the algorithm

whatever the query, You will have to carry on additional procesing for the rounding

( that' s what looks like You are asking for )

given the result as yyyy/mm/dd You will have to compute

years = yyyy ( if mm <= 6 )
years = yyyy + 1 if ( mm > 6 )
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Wed Apr 08, 2009 11:01 am    Post subject: Reply to: Difference between two db2 dates in years
Reply with quote

The requirement was to check whether a given date yyyy-mm-dd was more than 2yrs in future. So I was subtracting that particular date yyyy-mm-dd from current date.

But as this did not work, i am now using a function timestampdiff

SET :DIFF-IN-MONTHS = TIMESTAMPDIFF(64,CHAR(
TIMESTAMP(DATE(:date-to-be-checked),'00.00.00') -
TIMESTAMP(DATE(:current-date),'00.00.00')))

Here 64 stands for months.

And my program now checks whether DIFF-IN-MONTHS >24(months) and hence the problem is now solved.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1239
Location: Richfield, MN, USA

PostPosted: Wed Apr 08, 2009 9:40 pm    Post subject:
Reply with quote

Thanks for getting back with your solution.
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 Difference between TWALENG and TWASIZE Arunkumar Chandrasekaran CICS 3 Tue Jan 03, 2017 12:57 pm
No new posts Sticky: difference between ... DUMMY ... and ... enrico-sorichetti JCL & VSAM 0 Mon Oct 17, 2016 4:31 pm
No new posts Mainframe Openings for 3 to 5 years i... muralikrishnan_new Mainframe Jobs 0 Fri Jun 17, 2016 4:48 pm
No new posts Difference space showed for TS and it... autobox DB2 1 Thu Apr 14, 2016 1:07 pm
No new posts Difference Between CICS WebSupport an... sachinji84 CICS 1 Mon Mar 07, 2016 7:25 pm


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