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