tensixtyone

Rants of Andrew Williams / Nik_Doof

SQL Server Last Full Week

with 5 comments

While writing a new report today I’ve been drove mad while creating a dynamic query that selects the last full week. So here is the code for SQL Server, to save anyone else the pain:

dateadd(dd,0, datediff(dd,0,
   dateadd(day,-1*datepart(weekday,getdate())+1,dateadd(week,-1,getdate()))
))
dateadd(dd,0, datediff(dd,0,
   dateadd(day,7,dateadd(day,-1*datepart(weekday,getdate()),dateadd(week,-1,getdate())))
))

Written by Andrew Williams

September 17th, 2008 at 2:12 pm

Posted in Projects

Tagged with , , , ,

«
»

5 Responses to 'SQL Server Last Full Week'

Subscribe to comments with RSS or TrackBack to 'SQL Server Last Full Week'.

  1. Thanks Andrew: You saved me a ton of time.

    Troy

    3 Feb 09 at 3:32 pm

  2. Bless you! This totally made my day, and its a Monday!!! Whoo hoo!

    Lisa

    22 Jun 09 at 2:14 pm

  3. Thank you so much for saving me a bunch of time!!!

    Christina

    18 Mar 10 at 10:27 am

  4. Wouldn’t this be a far simpler way? Perhaps I’m missing what your goal is here.

    …WHERE YourCreatedDate > dateadd(day, -7, getdate())

    Cory

    15 Apr 10 at 2:49 pm

  5. Yes, slightly different goal. The idea is to get the last full week period, so your query would only return the date 7 days ago, whereas the one in the article would return Monday – Sunday last week.

    Andrew Williams

    15 Apr 10 at 3:16 pm

Leave a Reply