Cheat Sheet for Calculating Important Dates

Report writers and database developers are called upon frequently to code-out "keystone dates," such as start of the month, end of the month, end of the year, and so forth.  What if you had a cheat sheet for those calculations? 

Well, now you do!

Related: Correctly Calculating Datetime Differences

First Day of the Year

--First Day of Last YearSELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()) - 1 , '19000101') AS [First Day of Last Year];GO--First Day of This YearSELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()), '19000101') AS [First Day of This Year];GO--First Day of Next YearSELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()) + 1 , '19000101') AS [First Day of Next Year];GO

Last Day of the Year

--Last Day of Last YearSELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()), '19000101')) AS [Last Day of This Year];GO--Last Day of This YearSELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()) + 1 , '19000101')) AS [Last Day of This Year];GO--Last Day of Next YearSELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()) + 2 , '19000101')) AS [Last Day of Next Year];GO

First Day of the Month

-- To Get First Day of Previous MonthSELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - 1, '19000101') AS [First Day Previous Month];GO -- To Get First Day of Current MonthSELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101') AS [First Day Current Month];GO-- To Get First Day of Next MonthSELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 1, '19000101') AS [First Day Next Month];GO

Last Day of the Month

-- To Get Last Day of Previous MonthSELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101'))as [Last Day Previous Month];GO -- To Get Last Day of This MonthSELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 1, '19000101'))as [Last Day This Month];GO-- To Get Last Day of Next MonthSELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 2, '19000101'))as [Last Day Next Month];GO

Start of the Day

-- To Get Midnight YesterdaySELECT DATEADD(d, -1, DATEDIFF(d, 0, GETDATE())) AS [Midnight Yesterday];-- To Get Midnight TodaySELECT DATEADD(d, -0, DATEDIFF(d, 0, GETDATE())) AS [Midnight Today];-- To Get Midnight TomorrowSELECT DATEADD(d, 1, DATEDIFF(d, 0, GETDATE())) AS [Midnight Tomorrow];

Other Dates/Times of Interest

--To Get 11:59:59 YesterdaySELECT DATEADD(ss, (60*60*24)-1, DATEADD(d, -1, DATEDIFF(d, 0, GETDATE()))) AS [11:59:59 Yesterday];--To Get Noon YesterdaySELECT DATEADD(hh, 12, DATEADD(d, -1, DATEDIFF(d, 0, GETDATE()))) AS [Noon Yesterday];--To Get 11:59:59:997 YesterdaySELECT DATEADD(ms, (1000*60*60*24)-2, DATEADD(d, -1, DATEDIFF(d, 0, GETDATE()))) AS [11:59:59.997 Yesterday];

Design Patterns

If these don't provide the full range of dynamic date formulas you need for reporting or querying, you should be able to identify the design patterns for the offsets necessary to fulfill what you need. By altering offsets in the formulas provided above, you should be able to modify the code to get you what you need; not just for yesterday, today, or tomorrow, but also for years to come.

Related: Date Operations Made Easy