date difference in SQL 1

[ari1]

In Orcale there is a function MONTHS_BETWEEN and in SQL Server DateDiff which help to calculate age from a birthdate. Is there a similar function in DB2 to get a difference of dates (in months)?

 

[MarcLodge]

In DB2, you would use arithmetic calculation and the MONTHS to get the difference (I think). If I remember rightly the syntax is:

SELECT MONTHS(datefield1) - MONTHS(datefield2)

I'm not 100% certain of this as I'm at home away from the mainframe, but let me know how you get on.

Marc

 

[ari1]

Thanks, it looks ok, but there is no MONTHS -function, at least not in UDB2 version 7 (NT). The problem is, that difference between
1990-06-19 and 2002-06-19 => 12 years, and the differnece 1990-06-20 and 2002-06-19 => 11 years.
No solution in DB2?

 

[MarcLodge]

Ari,
Please ignore my last post as it was utter rubbish! That's the problem you get from with with too many languages, you forget the details of some of them if you don't work with them for a while.

If you subtract one date from another, the answer that you get back is in the format YYMMDD. If you run a query to subtract 1990-06-19 from 2002-06-19 you should get back 120000 ie. 12 years no months no days, which in my book is correct. If you run a query to subtract 1990-06-20 from 2002-06-19 you should get back 111129 which also seems ok to me.

DB2 dates functions aren't as clever as they could be, but if you do want to calculate the number of months between two dates, you can do it using SQL as such:

SELECT
(DECIMAL(SUBSTR(DIGITS(datefield1 - datefield2),3,2)) * 12)
+ (DECIMAL(SUBSTR(DIGITS(datefield1 - datefield2),5,2)))

There may be a more elegant way of doing it (surely there is???) but this will work.

HTH
Marc

 

[ari1]

Marc
I figured out myself this date -difference thing too. Your examples were right. Your query works, but gives months- between and needs to be divided with 12 and rounded.

This is maybe simpler:

select integer(floor( (datefield1 - datefield2) / 10000 ) as age ...

OR

select
decimal((SUBSTR(DIGITS(datefield1 - datefield2),3,2)) ) as age ...

First query: diving the yymmdd format with 10000, then function floor (eg. age 12,0813 gets 12,0) and finally integer is the age.

cheers
Ari

 

[wing2x]

I believe to get the year difference between two dates (i.e. age) the year function works in DB2:

year(datefield1 - datefield2)

The month function &quot;month(datefield1 - datefield2)&quot; returns the difference in months but only in the range of -11 to 11. Positive if datefield1 > datefield2 negative if datefield1 < datefield2; zero if difference is not greater than 1 month.

For example, month(date('2002-06-28') - '2001-07-28') is 11; month(date('2002-06-28') - '2000-07-28') is also 11;
month(date('2002-06-28') - '2002-07-28')
is -1; month(date('2002-06-28') - '2003-07-28') is also -1.

The ff expression would return the difference in months between two dates:

(year(datefield1 - datefield2)*12)+ month(datefield1 - datefield2)

 

[nemer1]

Does anyone know how to express the 'duration' value
in the form of total number of days within that duration ?
(and I mean 'exact' total-numebr-of-days, and not an approximate total based on a 30-day per month assumption).

i.e.
the query:
SELECT DATE ('03/01/2004') - '12/01/2003'
returns a duration of 00000300 (i.e. 3-months).
And those 3-months encompasses:
a 29-day February plus a 31-day-January plus a 31-day-December (total 91 days).

So I would be looking for a query which would return the number: 91. Any ideas ?


November

i.e. 2003-01-01 less 2002-08-01
contains a number of

 

[MarcLodge]

Nemer1,
Place the words DAYS around both of your date operands as in:

SELECT DAYS(DATE('2004-03-01')) - DAYS(DATE('2003-12-01'))

that should return you a value of 91

Marc