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!

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

Comments

Plain text