Difference between two db2 dates in years

Debasis Misra · New User Joined: 16 Sep 2008 Posts: 72 Location: Bangalore

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: 21

i don't understand what you exactly mean!

when you subtract two date you get a result like this

dick scherrer · Posted: Tue Apr 07, 2009 9:43 pm

Hello,

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

Quite a difference. . . .

enrico-sorichetti · Posted: Tue Apr 07, 2009 10:33 pm

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 · New User Joined: 16 Sep 2008 Posts: 72 Location: Bangalore

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 · Posted: Wed Apr 08, 2009 9:40 pm

Thanks for getting back with your solution.