Denham Coder

Developing TSQL summary reports by some variation of date is a very common requirement. Therefore having a library of date expressions is very useful. Below are a group of the common expressions I use.

First and Last Day of the Previous Month

SELECT 
CAST(GETDATE() AS DATE) AS [Today],
CAST(DATEADD(dd, -(DAY(DATEADD(mm, 1, GETDATE()))-1), DATEADD(mm, -1, GETDATE())) AS DATE) AS [First Day of Previous Month],
CAST(DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) AS DATE) AS [Last Day of Previous Month]

First and Last Day of the Current Month

SELECT 
CAST(GETDATE() AS DATE) AS [Today],
CAST(DATEADD(dd, -(DAY(GETDATE())-1), GETDATE()) AS DATE) AS [First Day of Current Month],
CAST(DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE())+1, 0)) AS DATE) AS [Last Day of Current Month]

First and Last Day of the Next Month

SELECT 
CAST(GETDATE() AS DATE) AS [Today],
CAST(DATEADD(dd, -(DAY(DATEADD(mm, 1, GETDATE()))-1), DATEADD(mm, 1, GETDATE())) AS DATE) AS [First Day of Next Month],
CAST(DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE())+2, 0)) AS DATE) AS [Last Day of Next Month]

First and Last Day of the Current Year

SELECT 
CAST(GETDATE() AS DATE) AS [Today],
CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS DATE) AS [First Day of the Year],
CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS DATE) AS [Last Day of the Year]

First and Last Day of the Previous, Current and Next Quarters

SELECT
CAST(GETDATE() AS DATE) AS [Today],
CAST(DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0) AS DATE) AS [First Day of the Previous Quarter],
CAST(DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)) AS DATE) AS [Last Day of the Previous Quarter],
CAST(DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0) AS DATE) AS [First Day of the Current Quarter],
CAST(DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0)) AS DATE) AS [Last Day of the Current Quarter],
CAST(DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0) AS DATE) AS [First Day of the Next Quarter],
CAST(DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +2, 0)) AS DATE) AS [Last Day of the Next Quarter]