Date / time range

[jack_schueler]

Although I have answered a few, I think it's the first time I have ever posed a question on this forum.

SQL Anywhere documentation has stated for years that "the useful range of TIMESTAMP date types is from 1600-02-28 23:59:59 to 7911-01-01 00:00:00".

The truth seems to be "the useful range of TIMESTAMP date types is [0001-01-01 00:00:00, 7911-01-01 00:00:00)" using set notation, or "the useful range of TIMESTAMP date types is from 0001-01-01 00:00:00 up to, but not including, 7911-01-01 00:00:00". This statement allows for fractional seconds following 7910-12-31 23:59:59.

OK, so my question is, where did this 1600-02-28 23:59:59 come from? Dusting off a Watcom SQL 4.0 tome, I read that it may have been true in Watcom SQL 3.0. But after that, I don't believe it ever was true. But I can't believe that someone just invented this special date.

Or is it true, and there is some situation of which I am not aware where dates earlier than this return incorrect results?

[justin_willey]

It's when Scotland moved to having 1st January as the first day of the year (instead of Candlemass) but I can't imagine that's the reason!

I'm glad I wasn't involved in IT then - can you imagine the fun of trying to work out whether 1599-02-15 was before or after 1600-02-14. I'm not even sure if I have that example right.

[VolkerBarth]

May this be due to the Gregorian calender, the date being the day before the first leap day in a "00" year after the Gregorian reform? (Though it would have been a leap year according to Julian calendar, too.)

The Gregorian reform makes timespan calculations in 1582 tricky for those cultures affected, but if that woudl be the reason for this documented limitation, it should apply to the DATE datatype, too, and that one seems to support [0001-01-01;9999-12-31].

[jack_schueler]

There is the related claim that hours and minutes are tossed for dates before 1600-02-28 23:59:59 (false) which makes them less "useful". Since DATE has no hours/minutes the claim is moot for that data type.

[Vlad]

1600 is what (according to my Google skills) is the epoch time that MS uses for a don't-remember-type. However MS has a 100 ns precision, and SA probably a second. The question is what to do with so many seconds?! Of course fit as many years as possible 🙂

I do not know the sizeof TIMESTAMP. Maybe it is close to 4-bytes… But I don't know why the 28th of February.

[VolkerBarth]

Hm, the classic MS SQL Server (and ASE!) datetime data type starts with 1753-01-01 and its precision is limited to about 1/333 seconds (i.e. the first four values are '1753-01-01 00:00:00.000', '1753-01-01 00:00:00.003', '1753-01-01 00:00:00.007', '1753-01-01 00:00:00.010') although its storage size if 8 bytes.

The historical reasons seem to be disucessed here:

What is the significance of 1/1/1753 in SQL Server?

Therefore I think SQL Anywhere's datetime and time data types have always supported a much higher precision (1 µs), even when the OS/hardware may not provide for that... - and I'm quite sure that the original Watcom/PACEBASE implementation had not Sybase/MS SQL Server compliance in mind:)