Day Intervals in ANSI SQL

[tdatgod]

Hi,
According to the ANSI standard

a timetamp - a timestamp == an Interval

a timestamp can be a date, time or timestamp field

an interval can have may different UNITS

year
month
day
hour
minute
second
year to month
day to hour
day to minute
day to second
hour to minute
hour to second
minute to second


the one of interest to me is Day.


select date1 - date2 day(4);

This allows a range of -9999 to 9999 days.

select date1 - date2 day(5);
^
|

isn't allowed.....


Why does ANSI limit it to 9999 days? This basically limits the Day interval to about 27 years.

What if you need to know the number of days between 2 dates and it is greater than 27 years?

Is there some ANSI SQL that given 2 date fields can calculate the number of days between them without having to worry about this 27 year limit?

I know ODBC provides some functions for calulating the number of Days since the beginning of the georgian calander or 1900 and a few vendors may provide extensions to ANSI to provide this functionality, but...

This is the ANSI SQL forum and I want to know if there is an ANSI SQL compliant way to solve this issue.

--

 

[tlbroadbent]

I believe the range for years is -9999 to 9999 in the ANSI SQL-92 standard. Why do you think that is the range of days? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tdatgod]

Hi,
Well you are correct. I went and found our copy of the ANSI SQL standard and Year is limited 9999, however for the others....


&quot;The maximum value of the <Interval leading field precision> is implementation-defined, but shall not be less than 2.

Therefore I guess it is the implemenation I am using, my own Teradata, that limits it to '4'.


Thanks for pointing this out. I will open up a change request to up the limit.

--