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