Recently I started a series of articles where I’m covering datetime

calculations. In
DATETIME Calculations, Part 2 (InstantDoc #94819) I

discussed start and end of period calculations. For example, to calculate the

start date of the current month, I provided the following expression:

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0);The constant 0 represents the base date January 1, 1900. The DATEDIFF

function calculates the difference in terms of months between the anchor and

the input datetime value (call that difference diff ). The DATEADD function

then adds diff months to the anchor datetime value. Because the anchor's

day unit is 1 (the first of the month), and you add whole months, you get the

first day of the month corresponding to the input datetime value. To get the

last day of the month, add diff plus one more month, and finally subtract one

day:

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0) - 1;Adding diff plus one month produces the first day of the next month.

Subtracting one day produces the last day of the current month.

Peter Larsson suggested a simplification for the end of period calculation.

Use the constant -1 as the anchor date (December 31, 1899) instead of 0

(January 1, 1900). Since this
anchor date (-1) is the last day of a month (as

well as quarter and year), you
don’t need to add one month to get the first

day of the next month, and then
subtract one day to get the last day of the

current month. Here’s how the
simplified expression producing the last day

of the current month looks like:

SELECT DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1);Similarly, to get the last day of the current quarter, simply specify quarter as

the date part:

SELECT DATEADD(quarter, DATEDIFF(quarter, -1, GETDATE()), -1);And to get the last day of the current year, specify year as the date part:

SELECT DATEADD(year, DATEDIFF(year, -1, GETDATE()), -1);I find this to be a nice tip and would like to thank Peter Larsson for sharing!

Cheers,

--

BG