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 Year                              SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()) - 1 , '19000101')                               AS [First Day of Last Year];                              GO                              --First Day of This Year                              SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()), '19000101')                               AS [First Day of This Year];                              GO                              --First Day of Next Year                              SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()) + 1 , '19000101')                               AS [First Day of Next Year];                              GO                              


Last Day of the Year

                              --Last Day of Last Year                              SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()), '19000101'))                               AS [Last Day of This Year];                              GO                              --Last Day of This Year                              SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()) + 1 , '19000101'))                               AS [Last Day of This Year];                              GO                              --Last Day of Next Year                              SELECT 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 Month                              SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - 1, '19000101')                               AS [First Day Previous Month];                              GO                               -- To Get First Day of Current Month                              SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')                               AS [First Day Current Month];                              GO                              -- To Get First Day of Next Month                              SELECT 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 Month                              SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101'))                              as [Last Day Previous Month];                              GO                               -- To Get Last Day of This Month                              SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 1, '19000101'))                              as [Last Day This Month];                              GO                              -- To Get Last Day of Next Month                              SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 2, '19000101'))                              as [Last Day Next Month];                              GO



Start of the Day

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



Other Dates/Times of Interest

                              --To Get 11:59:59 Yesterday                              SELECT DATEADD(ss, (60*60*24)-1, DATEADD(d, -1, DATEDIFF(d, 0, GETDATE())))                               AS [11:59:59 Yesterday];                              --To Get Noon Yesterday                              SELECT DATEADD(hh, 12, DATEADD(d, -1, DATEDIFF(d, 0, GETDATE())))                               AS [Noon Yesterday];                              --To Get 11:59:59:997 Yesterday                              SELECT 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