0

I'm running a query to list all dates contained within a table, along with the corresponding 'ISO Week Number' and 'Week Commencing' date in accompanying columns. I'm using SQL Server 2008 R2.

Just in case UK differs from other regions: my 'Week Commencing' is based on Monday being the start of any week.

However, the query I'm using produces a result that appears to 'offset' the date by a day.

The code which calculates the ISO week number is correctly assigning the right week number to each date. However, the code I'm using to also calculate the Week Commencing date isn't.

For example: for 'Week Commencing Monday 18th December 2017', I'm expecting my result table to show 7 grouped lines for the date range Mon 18th - Sun 24th.

However, what I'm getting is an offset 7 day range that goes Sun 17th Dec - Sat 23rd Dec, like this:

Start Date | Week Number | Week Commencing
------------------------------------------
17/12/2017 |      50     |  18/12/2017 
18/12/2017 |      51     |  18/12/2017
19/12/2017 |      51     |  18/12/2017
20/12/2017 |      51     |  18/12/2017
21/12/2017 |      51     |  18/12/2017
22/12/2017 |      51     |  18/12/2017
23/12/2017 |      51     |  18/12/2017
24/12/2017 |      51     |  25/12/2017

So the function is correctly calculating the Week Commencing Monday dates of the 18th and 25th respectively, and it's also correctly assigning 'Week Number 51' to the date range 18th-24th, but it's incorrectly listing Sun 17th as being in w/c 18th, Sun 24th as being in w/c 25th, etc...

As mentioned in the title, I've used the SET DATEFIRST 1; function, but to no avail.

This is the code I'm using:

USE [HDM_Local]

SET DATEFIRST 1;

SELECT 
       CONVERT(VARCHAR(10),Appt_Start_Date,103) [Start_Date]
       ,DATEPART(isowk,Appt_Start_Date) [Week Number]
       ,CONVERT(VARCHAR(10),(dateadd(wk, datediff(wk, 0, Appt_Start_Date), 0)),103) [Week Commencing]

FROM 
    Outpatients.vw_Appointments

GROUP BY
    Appt_Start_Date

ORDER BY 
    Appt_Start_Date ASC

I've played around with altering the parameters contained within the datediff function, but this doesn't align the dates.

Please can anyone advise if I'm missing something?

Jon295087
  • 731
  • 1
  • 8
  • 28
  • "my 'Week Commencing' is based on Monday being the start of any week." That's not what the ISO week number is based on, though. Week 1 is the week with the first Thursday in it. A solution based on adding and subtracting week numbers is doomed to fail -- at the very least when straddling years. Look at [this question](https://stackoverflow.com/q/1267126/4137916) instead. – Jeroen Mostert Aug 21 '17 at 09:59
  • @JeroenMostert - I don't disagree with you. When I read about the ISO week specification on the Microsoft site it explained the same; that's it's based on the Thursday. However, when I tried it for the first time (in this query) I was (a little) surprised to find it provided the result I was after, so I haven't questioned it because I'm more perplexed over the 'week commencing' calculation. – Jon295087 Aug 21 '17 at 10:04

0 Answers0