Freigeben über

SQL Nugget - Dates are illogical

  • Artikel

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

  • Anonymous
    August 20, 2010
    The comment has been removed

  • Anonymous
    August 21, 2010
    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

  • Anonymous
    August 21, 2010
    The comment has been removed

  • Anonymous
    August 22, 2010
    The comment has been removed

  • Anonymous
    August 22, 2010
    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.

  • Anonymous
    August 23, 2010
    The comment has been removed

  • Anonymous
    August 23, 2010
    The comment has been removed

  • Anonymous
    August 23, 2010
    The comment has been removed