Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Nugget - Dates are illogical

SQL Nugget - Dates are illogical

  • Comments 8
  • Likes

Whether I am reading Chocky by John Wyndham or the 12 Caesers by Suetonius the illogical nature of a calendars can be annoying . In Chocky the alien wonders why we don’t have 16 months instead of 12 and why weeks aren’t made up of 8 days, and in the 12 Caesers successive emperors try to make sure that a year really is a year by adding extra months(July and August).  Months can also catch you out in SQL Server:

If I run this

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

I will get back 

2010-08-30

because SQL Server has simply subtracted one month of the month number .  However what I was probably hoping for was the last day of the previous month as the 30th September is the last day of the month.  if you want to be fire proof and make no assumption about the day of the month that’s passed in then you could:

  • ignore the day of the month that’s passed in by replacing it with the first of the month i.e. ‘01’
  • take a day of this to give you the last day of the previous month

which in T-SQL looks like this

SELECT DATEADD(dd,-1,LEFT(CONVERT(VARCHAR(10),’20100930’,112),6) + ‘01’)

For extra credit can anyone  tell me what this should return and why , assuming you are in the UK like I am?

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

Comments
  • 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

  • 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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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)

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

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment