SQL Server Magazine

In this case—unlike the expression calculating offset—the base date you use doesn't really matter, as long as you specify the same base date in both the DATEDIFF function and the DATEADD function. The complete expression looks like

-- Last Tuesday (inclusive)
  SELECT DATEADD(day, DATEDIFF
  (day, 0 /* Any Base Date
  */, GETDATE()) -- diff
  (DATEDIFF(day, 1 /* Base
  Tuesday Date */, GETDATE()) %
  7), -- offset 0 /* Any Base
  Date */);

If you're after the most recent Sunday's date, all you need to do is specify a Sunday base date (the integer 6 or the string 19000107) instead of a Tuesday base date:

-- Last Sunday (Inclusive)
  SELECT DATEADD(day, DATEDIFF(day,
  0 /* Any Base Date */,
  GETDATE()) - (DATEDIFF(day,
  6 /* Base Sunday Date */,
  GETDATE()) % 7), 0 /* Any Base
  Date */);

Datepart. You can also rely on the datepart-based technique to calculate offset. Remember that the datepart-based technique calculates a language-independent weekday number. If you think about it, offset is nothing more than a weekday number minus one, assuming the weekday you're looking for is the first day of the week. The following expression uses the datepart-based technique to calculate a language-independent weekday number, assuming Monday is the first day of the week:

DATEPART(weekday, GETDATE() + @@
  DATEFIRST - 1 /* datefirst is 
  Monday */) 

Don't confuse this expression's constant (1 for Monday) with the constants in the datediff-based technique. This time, the constant doesn't represent a base date as an integer; rather, it represents the logical first day of the week you want to set—1 for Monday, 2 for Tuesday, and so on. So, to get offset (weekday number minus one), assuming Monday is the first day of the week, use the expression

DATEPART(weekday, GETDATE() + @@
  DATEFIRST - 1 /* datefirst is
  Monday */) - 1

To get offset assuming Tuesday as the first day of the week, subtract the constant 2 from @@DATEFIRST:

DATEPART(weekday, GETDATE() + @@
  DATEFIRST - 2 /* datefirst is
  Tuesday */) - 1

Finally, embed the new offset calculation in the complete expression that returns the last occurrence of a weekday—for example, the most recent Tuesday:

-- Last Tuesday 
  SELECT DATEADD(day, DATEDIFF(day,
  0 /* Base Date */, GETDATE()) -
  (DATEPART(weekday, GETDATE() + 
  @@DATEFIRST - 2 /* datefirst is
  Tuesday */) - 1), 0 /* Base
  Date */);

To return last Sunday's date, subtract the constant 7 from @@DATEFIRST:

-- Last Sunday 
  SELECT DATEADD(day, DATEDIFF(day,
  0 /* Base Date */, GETDATE()) -
  (DATEPART(weekday, GETDATE() +
  @@DATEFIRST - 7 /* datefirst is
  Sunday */) - 1), 0 /* Base
  Date */); 

I find this article's first datediff-based technique to be the most elegant way to calculate the last occurrence of a weekday. Doubtless, it's the shortest. So, from this point on, I'll rely on this technique for other calculations as well. As a reminder, here's the expression I used to calculate the last occurrence of a Monday:

SELECT DATEADD(day, DATEDIFF(day,
  0, GETDATE()) /7*7, 0);

Twice, you specify a base date representing the weekday you're after (the integer 0 or the string 19000101 for a Monday base date).

Suppose you need the calculation of the last occurrence of a weekday to be exclusive—not to take the event date into consideration. All you need to do is subtract 1 from the event date. For example, to produce the most recent occurrence of Monday in an exclusive manner, you would use the expression

-- Last Monday (exclusive)
  SELECT DATEADD(day, DATEDIFF(day,
  0, GETDATE()-1) /7*7, 0);

This method is applicable to all the techniques that I demonstrated earlier. Simply subtract 1 from the event date to make the calculation exclusive.

Prev. page     1 [2] 3     next page