Get common dates in T-SQL

  • Articolo

A few years ago I created an article around Reporting Services and dates. It could have been written more generically, because I reference this quite a bit to get common dates like "the beginning of this week", "midnight last night", etc, in my SQL queries. It's a fairly comprehensive list of relative dates that one might want to get in T-SQL for reporting, scheduling, etc.

It can get pretty complex, such as this function for getting the end of the current week

CREATE FUNCTION get_week_end (@date datetime)
RETURNS datetime AS
BEGIN
   return dateadd(yyyy, datepart(yyyy,
      dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
    + dateadd(ms, -3,
      dateadd(dy, datepart(dy,
     dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
END

If you don't find what you need, you can typically use the dateadd function to tweak one of these. Here is the complete list outlined in the article:

  • Start of this week
  • End of this week
  • Start of last week
  • End of last week
  • Start of this month
  • End of this month
  • Start of last month
  • End of last month
  • Start of the day yesterday
  • End of the day yesterday
  • Start of the day today
  • End of the day today
  • Start of the day this Monday
  • End of the day this Monday
  • Start of the year
  • Tomorrow at noon
  • Today at noon