### TSQL Tips – Useful Date Calculations

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(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(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]``` 