SQL Nugget - Dates are illogical

re: SQL Nugget - Dates are illogical

Richard — Mon, 23 Aug 2010 15:22:21 GMT

"... reading Chocky by John Wyndham ..."

Great - now I can't get the theme-tune from the TV series out of my head!

re: SQL Nugget - Dates are illogical

Andrew.Fryer — Mon, 23 Aug 2010 07:36:48 GMT

Thanks for all the commments, first up I didn't declare what type of date was being used here , but SQL Server form 2008 will not returen an error for this and does in fact return 10 sep 1752 on my US based virtual machine. However as many of you spotted this didnt't never existed in the UK, because we swapped calendars. so Karl if you want to send me your contact deatils you'll get the SQL Server r2 polo shirt and say hi if you can make it to SQL Bits in York

Andrew (afryer@microsoft.com)

re: SQL Nugget - Dates are illogical

Janis Norvelis — Mon, 23 Aug 2010 07:12:16 GMT

I am not from the UK, but the last query should return NULL or maybe blow up, because there was no 10th of September in 1752.

re: SQL Nugget - Dates are illogical

Ken — Mon, 23 Aug 2010 02:44:36 GMT

Interesting trivia about 10/10/1752. I didn't know that until I googled/binged.

However, the SELECT statement is returning an error, probably because it implicitly convert '17521010' into datetime data type, which has a minimum value of 1753-01-01. If I explicitly convert it to datetime2 first, then SQL Server returns the date 1752-09-10, which didn't really exist.

re: SQL Nugget - Dates are illogical

cgw — Sun, 22 Aug 2010 18:01:39 GMT

I know that for SQL 2005 and earlier, the earliest date you can store in a datetime field is '1753-01-01', because this is the first full year after the United Kingdom finally adopted the Gregorian calendar.

The loss of 11 days happened between Sept. 2, 1572 and Sept. 14, 1572. So your query would not result in 1752-09-10, but I would guess you would want it to return 1752-08-30.

re: SQL Nugget - Dates are illogical

Professor Davos — Sun, 22 Aug 2010 06:38:45 GMT

This is when the British calendar changed from Julian to Gregorian and the days between the 3rd of September and the 13th of September were skipped. I am in Australia and this year predates the 1788 British colonisation.

Subtracting a month from the 10th of October could put you back in August sometime, but how long is a month?

In my opinion, subtracting a month from a date should return the same 'dd' in the previous month. This is the only consistant outcome, so an 'out of range' error is appropriate. This is obviously the same conclusion that the real decision makers came to.

re: SQL Nugget - Dates are illogical

Andy — Sat, 21 Aug 2010 13:56:15 GMT

I'm not sure of the answer by there is a great book by David Ewing Duncan called "The Calendar" which should hold the answer, my copy is currently boxed up as I'm decorating at home. Alternative a trip to Greenwich to see the museum at the top of the hill would be fruitful

re: SQL Nugget - Dates are illogical

Karl Beran — Fri, 20 Aug 2010 12:02:56 GMT

SELECT DATEADD(MONTH,-1,’17521010’)

should return

1) error -in reality as SQL server by default does not support dates prior to 17530101 for datatypes datetime or smalldatetime (for which it will perform an implicit conversion for the string '17521010')

2) NULL -logically as this date didn't exist: on the switch to the gregorian calendar in england Wed 2 Sep was followed by Thu 14 Sep.

3) 1752-09-10 if you change the syntax to SELECT DATEADD(MONTH,-1,cast('17521010' as date)) or SELECT DATEADD(MONTH,-1,cast('17521010' as datetime2)) which wouldn't be a valid date in the UK but could be in spain, austria, hungary, turkey among others.

check out my other interesting date post at:

karlberan.spaces.live.com/.../cns!451BD4B395B5A4C5!222.entry