0

How can I get weeknumber of a date with Sunday as first day of the week and Start with the week that has at least four days in the new year, in short datepart equivalent of 

 DatePart("WW", datefield, vbSunday, vbFirstFourDays)?

Because for example, I want 1st January 2015 to return 53 instead of 1, as the first week of 2015 has 4 days though, but starts from Thursday instead of Sunday.

Hemant Sisodia
  • 488
  • 6
  • 23

1 Answers1

0

I don't know if this fulfills all your requirements, but there is ISO_WEEK. Try this:

SELECT DATEPART(ISO_WEEK,'20160103')

Which day is taken as the first day of the week is dependant on DATEFIRST. (See the comments about ISO_WEEK and DATEFIRST. In case of ISO_WEEK this is Monday in any case...)

Test your edge-cases...

If the standard (out-of-the-box) mechanisms don't meet your needs you should think about a calendar/number-table. You can widen this by any custom column. Any compute-this-with-some-magic-logic approach is more work and takes more time than to set some edge-values in a table manually. Furthermore, such a table will be faster and will help you in very many related cases.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Ref: [ISO First Week](https://en.wikipedia.org/wiki/ISO_week_date#First_week). NB: ISO Weeks start on Monday. – HABO Oct 15 '18 at 13:43
  • 1
    @HABO, Just tested it. Thx for the hint, ISO-Week is not looking at `@@DATEFIRST`. In this case I'd use a calendar/number-table and add a column with the ISO_WEEK. This I'd modify semi-manually for some edge cases... – Shnugo Oct 15 '18 at 13:53
  • Thanks @Shnugo , the Idea of Calendar/number-table with adjustment in some corner cases worked for me. – Hemant Sisodia Oct 16 '18 at 06:53