August 18, 2011

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

5 Responses to "SQL Server: Last Day Previous Month without DATEADD and DATEDIFF"
  1. sqlknight said...
    August 18, 2011 at 6:29 AM

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

  2. Madhivanan said...
    August 18, 2011 at 6:47 AM

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

  3. SQL Database Recovery said...
    August 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..

  4. Madhivanan said...
    August 23, 2011 at 4:01 AM

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

  5. Samuel Conaghan said...
    November 21, 2014 at 4:06 AM

    This shouldn’t matter as SQL Server will simply create a new log file for you.
    - SQL Server Restore Toolbox. http://www.sqlserver.restoretools.com// You can read up more on SQL Server here. http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server/498-creating-a-new-database-using-an-mdf-file?_=1416149856104/

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions