SQL Nugget - Dates are illogical

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