ANSI Date problem

[mbeylen]

Hi all,
I want to learn if it is possible to get month differece betweem two date fields. I mean, I have 2 fields on my table
BEG_DATE and END_DATE. I would like to obtain months between BEG_DATE and END_DATE. But I must do this using ANSI SQL commands.

Looking 4 hearing from u.

Thanks in adavance...

 

[r937]

select year(end_date)*100+month(end_date)
     - year(beg_date)*100+month(beg_date)
   as months_diff

note this will yield 1 month for the difference between 2002-08-31 and 2002-09-03, even though it's only 3 days

rudy

http://rudy.ca/

 

[r937]

aargh, forgot parentheses

select year(end_date)*100+month(end_date)
     - (year(beg_date)*100+month(beg_date))
   as months_diff

rudy

 

[tdatgod]

Hi,
I am reposting this from a post I did in the Teradata Forum about a year ago.


Hi,
ANSI SQL defines

timestamp - timestamp == an interval.

The problem it doesn't define what the default UNIT for interval. interval units can be...

year ( number of years )

month ( number of months )

year to month ( number of years + remaninder months )

day ( number of days Maximum 9999 days ( 27 years ))

day to hour ( number of days + remainder hours )

day to minute ( number of days + remaninder hour:minutes )

day to second ( number of days + remainder hour:minute:second.sec)

hour ( number of hours maximum 9999 hours ( 13 months ))

hour to minute ( number of hours + remainder minutes )

hour to second ( number of hours + remainder minute:seconds.sec)

minute ( number of minutes 9999 minutes ( 1 week ))

minute to second ( number of minutes + remainder seconds )

second ( number of seconds )

Note that all these are singular and not plural ( hour not hours ). Again this is ANSI SQL requirement.


therefore your SQL should be more like....

sel End_date - Beg_date month(4)
from blort;

(4) asks it to use 4 digits format 9999. the Maximum precision is 6 for seconds ( which is actually the decimal portion of seconds ) and the maximum number of digits for the Year is 4 ( or 9999 ).


Again that is ANSI. All the others are vendor specific. For example teradata limits Months to 4 digits. if you fail to specify the percision what you get is vendor specific. For example it may default to 1 digit in which case any answer over 9 months will result in a

Interval field overflow

so don't forget the precision parameter.



9999 months is about 833 years.

Now again just because this is ANSI doesn't mean every vendor supports this.

Teradata does.