SQL Server – Calculate Yesterday/Today of Last Month/Year

datedatetimesql serversql-server-2008

Is there a way to calculate today (and yesterday) of last month (and last year) in SQL?

Assuming today is 26-May-2015 then how to find?

  • Today of last year i.e. 26-May-2014?
  • Today of last month i.e. 26-April-2015?
  • Yesterday of last year i.e. 25-May-2014?
  • Yesterday of last month i.e. 25-April-2015?

Best Answer

Shouldn't really need any hacks to handle leap years, but it depends on what results you expect. Typically you just subtract the larger component first, so subtract a month before you subtract a day, instead of the other way around.

Do any of these produce results you don't expect? If so, which ones are "wrong" to you, and why?

DECLARE @d TABLE(d DATE);

INSERT @d(d) VALUES('20150330'),('20150331'),
      ('20150430'),('20150531'),('20150526'),

      ('20160229'), -- Leap day: test "Today of last year" and "Yesterday of last year"

      -- Month after leap day: test "Today of last month" and "Yesterday of last month"
      ('20160329'),('20160330'),('20160331'),

      -- March 1st in possible leap year: test "Yesterday"
      ('20160301'), -- test regular leap year
      ('20000301'), -- test century divisible by 400
      ('19000301'), -- test century not divisible by 400 (not a leap year)

      -- March 1st in year AFTER possible leap year: test "Yesterday of last year"
      ('20170301'), -- test regular leap year
      ('20010301'), -- test century divisible by 400
      ('19010301'); -- test century not divisible by 400 (not a leap year)

SELECT

  [Today] = d,
  [Today of last year] = DATEADD(YEAR, -1, d),
  [Today of last month] = DATEADD(MONTH, -1, d),

  [Yesterday] = DATEADD(DAY, -1, d),
  [Yesterday of last year]  = DATEADD(DAY, -1, DATEADD(YEAR, -1, d)),
  [Yesterday of last month] = DATEADD(DAY, -1, DATEADD(MONTH, -1, d)) 

FROM @d;

You could also consider using a calendar table if you want to handle leap years, holidays, weekends, etc. differently. What you really need to do is fully understand and explain your requirement. With many months having 31 days, and with leap years especially, each possible combination of "today or yesterday last month or year" can be open to multiple interpretations.

Related Question