SQL Server Magazine

Last month, in "DATETIME Calculations, Part 1" (InstantDoc ID 94487), I began a multipart discussion of datetime calculations. I talked about the challenges involved with having no separation between the date and time, and I shared techniques for extracting only the date or only the time by zeroing the other part.

This month, I continue the discussion, sharing techniques for calculating a certain period's start and end datetime values—for example, given an input datetime value, returning the corresponding first day of the month. I also share techniques for calculating a language-independent weekday.

Start/End Datetime Values
Last month, I showed you the following expression, which extracts only the date out of a given datetime value by zeroing the time (making it midnight). Again, I'll use the GETDATE() function as the input datetime value:

SELECT DATEADD(
day,
DATEDIFF(day, 0, GETDATE()),
0);

The DATEDIFF function calculates the difference in terms of days between an anchor datetime value—0, representing January 1, 1900, midnight—and the input datetime value—GETDATE(). Call that difference diff. The DATEADD function then adds diff days to the anchor datetime value. Because the anchor's time is midnight, and you add whole days, you get the target date at midnight.

You can use similar logic to calculate a period's start/end datetime values corresponding to a given input datetime value. For example, to calculate the first day of the month, provide an anchor date that is a first day of a month, and instead of using day units, use month units:

SELECT DATEADD(
month,
DATEDIFF(month, 0, GETDATE()),
0);

Remember that 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, simply 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.

Similarly, you can calculate the first day of the year. Simply specify a year unit instead of month:

SELECT DATEADD(
year,
DATEDIFF(year, 0,    
GETDATE()),
0);

To calculate the last day of the year, use

SELECT DATEADD(
year,
DATEDIFF(year, 0, 
  GETDATE()) + 1,
  0) - 1;

To calculate the start of the hour (zeroing the minutes, seconds, and milliseconds), use an hour unit:

SELECT DATEADD(
hour,
DATEDIFF(hour, 0, GETDATE()),
0);

To calculate the last minute of the hour, use

SELECT DATEADD( minute, -1,

  DATEADD(
  hour,
  DATEDIFF(hour, 0, GETDATE()) +1,
  0) );

   Prev. page   [1] 2 3     next page