SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



How to Calculate SQL Last Day of Month in SQL Server

In order to return the last day of the month of a specific date the following sql last day of month code scripts can be used.





Here is a sql method that can be used in order to calculate sql last day of month.
Within the below t-sql code, I used a sql variable in SQL DATE data type which is new with MS SQL Server 2008 enhancements. For previous SQL Server versions, sql developers can use sql DATETIME data type variable instead of DATE type variable.

DECLARE @date DATE = '20120214'
--DECLARE @date DATE = GETDATE()
DECLARE @LastDayofMonth DATE

SELECT
 @LastDayofMonth =
 DATEADD(
  DD,
  -DATEPART(DD,DATEADD(MM,1, @date)),
  DATEADD(MM,1, @date)
 )

SELECT @LastDayofMonth as [Last Day of Month]

Above t-sql select statement will return the calculated sql last day of month value as "2012-02-29" which is from a leap year date.

I thought that is a good way to test the tsql calculation for last day of a month. SQL developers can use GETDATE() SQL Server datetime function to find last day of current month.

The below select statement returns two calculated values; the first one is last day of month in DATETIME data type, the second one is in DATE data type.
The sql last day of month calculation is the same.
What is different for this case is the calculation of the first day of next month : YEAR(@date) * 10000 + (MONTH(@date) + 1) * 100 + 1

DECLARE @date DATE = '20120214'
--DECLARE @date DATE = GETDATE()

SELECT
 DATEADD(
 DD,
 -1,
 CONVERT(
  DATETIME,
  CAST(YEAR(@date) * 10000 + (MONTH(@date) + 1) * 100 + 1 as CHAR(8)),
  110
 )
 ) as LastDayofMonth
 ,
 DATEADD(
 DD,
 -1,
 CONVERT(
  DATE,
  CAST(YEAR(@date) * 10000 + (MONTH(@date) + 1) * 100 + 1 as CHAR(8)),
  110
 )
 ) as [Last Day of Month]

The above t-sql select script will return :
LastDayofMonth Last Day of Month
----------------------- -----------------
2012-02-29 00:00:00.000 2012-02-29






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems