Paul's MCS Developer Blog

Sharing and blabbing on my findings as a Microsoft consultant

June, 2008

  • Paul's MCS Developer Blog

    Get common dates in T-SQL


    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
       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) )

    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
  • Paul's MCS Developer Blog

    New SharePoint Developer Resource

    As part of the Capital Area .NET's new SharePoint SIG, I led a discussion group last week which worked together to create a WSS/MOSS Developer Productivity Wiki Page. Check it out on, and please feel free to add to it!
Page 1 of 1 (2 items)