Search code examples
sqlsql-serverdatediffdateadd

SQL Server 2012 - Week Date Sat To Friday

I think I'm really close to getting this but I'm just off the mark!

I'm trying to create a date (to report by) that will allow me select every week Saturday to Friday. Taking last week as an example the below code gives me the 29/07/2016 for a date range of 24/07/2016 to 30/07/2016 however I would like the date range to be 23/07/2016 to 29/07/2016 is this possible?

    DATEADD(WEEK,DATEDIFF(WEEK,0,[DateCreated]),4) AS [WeekCreated]

Many thanks in advance!

Id  Date    Week Commencing Week Ending
1   16/07/2016  16/07/2016  22/07/2016
2   17/07/2016  16/07/2016  22/07/2016
3   18/07/2016  16/07/2016  22/07/2016
4   19/07/2016  16/07/2016  22/07/2016
5   20/07/2016  16/07/2016  22/07/2016
6   21/07/2016  16/07/2016  22/07/2016
7   22/07/2016  16/07/2016  22/07/2016
8   23/07/2016  23/07/2016  29/07/2016
9   24/07/2016  23/07/2016  29/07/2016
10  25/07/2016  23/07/2016  29/07/2016
11  26/07/2016  23/07/2016  29/07/2016
12  27/07/2016  23/07/2016  29/07/2016
13  28/07/2016  23/07/2016  29/07/2016
14  29/07/2016  23/07/2016  29/07/2016
Solution

  • This should give you what you are looking for:

    DATEADD(DAY, 6 - DATEPART(weekday,[DateCreated]), [DateCreated])

    It will give you the following Friday for any given date. Keep in mind the weekday datepart is locale sensitive, so the offset may need to be adjusted depending on locale. Or you may need to do something more comprehensive to make it locale insensitive. SELECT @@DATEFIRST will give you the first day of the week in your locale. 7 is Sunday. You could incorporate that in if necessary.