Date diference

Database
Enthusiast

Date diference

Hi,

When i run this query :

Select ( cast('2011-06-21' AS date) - CAST('2010-06-30' as date) ) year(2);

In TD R2V6 return 0, but when run in TD 13.0 return 1

All settings are the same.

4 REPLIES
Junior Contributor

Re: Date diference

This was a "bug" (according to Standard SQL) which has been fixed in V2R6.2.
Now it's just like "Extract(year from date1) - extract(year from date2).

Most people prefered the old way to do the calculation :-)

Dieter
Enthusiast

Re: Date diference

Dieter, But if the days between the two dates is lower than 365, the result will be 0.

If not, 2011-01-01 - 2010-12-31 will be 1 !!!

I need use this for calc the age of a person.

Junior Contributor

Re: Date diference

You're right, this is how it's implemented.
Standard SQL and common sense sometimes differ :-)

If you want the age you can use a UDF, e.g. the existing Oracle MONTHS_BETWEEN typecasted to an INT.

Or use this calculation based on the internal storage of DATEs:

(CAST(date1 AS INT) - CAST(date2 AS INT)) / 10000

Dieter
Enthusiast

Re: Date diference

Thanks Dieter, with integers work fine.

BR
Dixxie