0

I am trying to use sysdate() to pull data for the previous month.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Carol
  • 11
  • 3

2 Answers2

3
DECLARE @firstOfLastMonth DATE;
SET @firstOfLastMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME())-1, 0);

SELECT ... FROM ...
WHERE dateColumn >= @firstOfLastMonth
AND dateColumn < DATEADD(MONTH, 1, @firstOfLastMonth);

Here's why you don't want to use BETWEEN for this:

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1

You mean the current server time minus one month? Try the DATEADD function:

dateadd(month, -1, sysdatetime())

If you want to select records with a column's value being in the past month, you could do:

SELECT foo
FROM bar
WHERE baz BETWEEN dateadd(month, -1, sysdatetime()) AND sysdatetime()
lc.
  • 113,939
  • 20
  • 158
  • 187
  • 1
    No, she meant for the previous month (e.g. January - but tomorrow that will mean February). – Aaron Bertrand Feb 28 '13 at 16:55
  • 1
    Also, if you mean `MONTH`, please type `MONTH`. Shorthand can get you into trouble. For example, do you think `DATEPART(y, GETDATE());` returns 2013 right now? A lot of people do. Don't be lazy. – Aaron Bertrand Feb 28 '13 at 17:05
  • @AaronBertrand Good point about shorthand getting you into trouble, but how in the heck did you read the OP's mind? – lc. Feb 28 '13 at 17:59
  • @lc. no super powers required. "Previous month" is a very typical phrase that *could* be interpreted as `exactly one month prior to now up to now` but almost always means `last month`. – Aaron Bertrand Feb 28 '13 at 18:10