• 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).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/