Getting first and last days of a given month

The following statements will allow you to manipulate date values from a date input (e.g. GETDATE()) to provide you with the last day of the given month.

--Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
--Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
--Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

I have found this very helpful.

You can also specify a hard-coded date, and wrap in CAST functions to clear the timespan

SELECT cAST(CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2015-06-01')+1,0)) AS DATE) AS DATETIME)

Original post is from SQLAuthority.com

Subscribe

0 comments