Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
Debasis Misra
Warnings : 1

New User

Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

 Posted: Tue Apr 07, 2009 10:02 am    Post subject: Difference between two db2 dates in years 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?

kregen

New User

Joined: 16 Mar 2006
Posts: 18

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

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
dick scherrer

Site Director

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

 Posted: Tue Apr 07, 2009 9:43 pm    Post subject: Hello, Notice that the original sql used "year" and the suggestion uses "date". Quite a difference. . . .
enrico-sorichetti

Global Moderator

Joined: 14 Mar 2007
Posts: 10457
Location: italy

 Posted: Tue Apr 07, 2009 10:33 pm    Post subject: Reply to: Difference between two db2 dates in years 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 )
Debasis Misra
Warnings : 1

New User

Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

 Posted: Wed Apr 08, 2009 11:01 am    Post subject: Reply to: Difference between two db2 dates in years 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.
Terry Heinze

JCL Moderator

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

 Posted: Wed Apr 08, 2009 9:40 pm    Post subject: Thanks for getting back with your solution.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Difference between EQUAL and EQUAL TO... jithinraghavan COBOL Programming 3 Thu Apr 19, 2018 2:22 am ISMF Difference between volume count ... upendrasri IBM Tools 2 Tue Dec 05, 2017 12:40 pm Comparing dates in different formats migusd SYNCSORT 4 Sat Nov 18, 2017 3:02 am Finding difference between two PS fil... Vignesh Sid SYNCSORT 5 Mon Oct 30, 2017 1:13 pm Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 5 Fri Sep 22, 2017 4:56 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us