Kevin Gill (1/1/2013)
Just to be picky, it actually won't work after the 28th Jan due to that dateadd of a month in there 😛
And to be even more picky, all three answer options have a mismatch in the parentheses. Here is the corrected version:
-- select 1
SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE()))),dateadd(mm,-1,GETDATE()))
-- select 2
SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE()))),GETDATE())
-- select 3
SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE()))),dateadd(mm,1,GETDATE()))
And for those who prefer to use efficient code to get the last day of the previous month, use either one of these options:
-- Option 1
SELECT dateadd(day, -datepart(day, getdate()), getdate());
-- Option 2
SELECT dateadd(month, datediff(month, getdate(), '20110101'), '20101231');
Option 2 has the additional advantage that you get only the day; all other options include a time part.
(None of this is intended as criticism of a question, just to provide some additional information).