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


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

4 comments:

Anonymous said...

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

Madhivanan said...

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

SQL Database Recovery said...

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

Madhivanan said...

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