SQL Server: Last Day Previous Month without DATEADD and DATEDIFF

Suppose you want to find the last day of the previous month, you can always use this method:

select dateadd(month,datediff(month,0,getdate()),0)-1

The above code finds the month difference between 1900-01-01 and current date and adds it to same date, so the result is first day of the month. Adding -1 to this result returns the last day of previous month. This is a very common approach where we use the DATEADD and DATEDIFF functions.

There is another method of finding the Last day of previous month where we will not use the dateadd and datediff functions. Here it goes:

select cast(getdate()-day(getdate()) as date)

The code shown above subtracts DAY from GETDATE(),  so it returns last day of previous month.

Casting it to the DATE data type gives only the date.

OUTPUT
Last Day Previous Month
at

4 comments:

  1. AnonymousAugust 18, 2011 at 6:29 AM

    Its worth noting that the DATE datatype is not compatible with versions of SQL before 2008.

    ReplyDelete
  2. MadhivananAugust 18, 2011 at 6:47 AM

    sqlknight, in that case you can convert it to datetime datatype

    ReplyDelete
  3. SQL Database RecoveryAugust 23, 2011 at 3:55 AM

    Before one month ago, I was also searching for the last day of the previous month but I too was stuck on this issue. One of my friend suggested me DATEADD and DATEDIFF functions approach that you have explained in the starting of your post. This approach is much better & easier then DATEADD and DATEDIFF functions approach. Thanks for sharing this post..

    ReplyDelete
  4. MadhivananAugust 23, 2011 at 4:01 AM

    SQL Database Recovery, thanks for the feedback and you are welcome

    ReplyDelete