3

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?
  • 2
    Which database are you using? Date functions are pretty different across database engines. You'll also need to define your terms a bit better. What is "today of last month" if today is March 31, 2015? Is it Feb 28, 2015? So "today of last month" is the same for March 28-31 in a non leap year? – Justin Cave May 26 '15 at 16:56
  • Yes, you right! I didn't gave a single thought about it. What can be workaround for this? – xameeramir May 26 '15 at 17:01
  • 2
    Also what is "today of last year" for 2016-02-29? – Aaron Bertrand May 26 '15 at 17:01
  • And how will you check today at least? – xameeramir May 26 '15 at 17:12
  • 2
    Just check out dateadd msdn.microsoft.com/en-us/library/ms186819.aspx if you're lucky, dateadd year / month work the way you want. – James Z May 26 '15 at 17:22
10

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.

  • 2
    There were some leap year tests missing that show that they are properly handled. I hope it was ok that I edited the answer to show them but I couldn't think of how to add any info in a comment that would properly explain it. But it certainly does appear that no hacks are needed, so +1. – Solomon Rutzky May 26 '15 at 22:12
6

Yesterday:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 1), 0)

Today of Last Year:

SELECT DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

Today of Last Month:

SELECT DATEADD(MONTH, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

Yesterday of Last Year:

SELECT DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 1), 0))

Yesterday of Last Month:

SELECT DATEADD(MONTH, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 1), 0))

Additional hack for odd dates (notably leap years):

(If date doesn't exist for the previous year, use the date prior to that)

  DECLARE @date DATETIME = '2016-03-1'

  SELECT CASE WHEN DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @date - 1), 0)) = DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @date), 0))
         THEN DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @date - 2), 0))
         ELSE DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @date - 1), 0))
         END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.