Simon Simon - 17 days ago 5
SQL Question

Islamic Week number

We are a company based in England and we also have another branch out in Dubai.
I must be missing something simple but how do I get the Islamic Week number from SQL server (2008 R2) - the server is based in England so we can't change much.

I've created the following test query to demonstrate the issue, (the column called "WhatWeWant" where i subtract -1 from the week number is to demonstrate the required answers within this example and NOT a fix)

DATEFIRST only affects the ww column and not the ISO_WEEK column.

SELECT
[xdate]
, DATEPART(ww, xdate) as ww
, DATEPART(ISO_Week, xdate) as iso_week
, DATEPART(ww, xdate) -1 as whatWeWant
FROM [test].[dbo].[testing]


results:

xdate ww iso_week whatWeWant
2016-11-18 00:00:00.0000000 47 46 46
2016-11-19 00:00:00.0000000 47 46 46
2016-11-20 00:00:00.0000000 48 46 47
2016-11-21 00:00:00.0000000 48 47 47
2016-11-22 00:00:00.0000000 48 47 47
2016-11-23 00:00:00.0000000 48 47 47
2016-11-24 00:00:00.0000000 48 47 47
2016-11-25 00:00:00.0000000 48 47 47
2016-11-26 00:00:00.0000000 48 47 47
2016-11-27 00:00:00.0000000 49 47 48
2016-11-28 00:00:00.0000000 49 48 48
2016-11-29 00:00:00.0000000 49 48 48

Answer

Try this:

SELECT
    [xdate]
  , DATEPART(ww, xdate) as ww      
  , DATEPART(ISO_Week, xdate) as iso_week
  , DATEPART(ww, xdate) -1 as whatWeWant
  , DATEPART(ISO_WEEK, DATEADD(DAY,1,xdate)) as whatIPropose
FROM [test].[dbo].[testing]

It seems to produce the expected result, regardless of the DATEFIRST setting.