1

The code returns:

set datefirst 1
select datepart(wk,'2016-01-01')  - 1

but

set datefirst 1
select datepart(wk,'2015-12-31')

returns..53 :/

But in fact - this is the same week. There is more days belonging to 2015 in this week, so it should be "53" or "1" (the same value) for any dates in this particular week. Is this possible to avieve this without building dedicated procedure to analyse date and adjust returned value ?

I use SQL Server 2005

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Rafal_PL
  • 41
  • 3
  • 5
  • Actually, it isn't the same week by most people's definition of a Week number. I'd expect Week 53 of the year 2016 to come in about 360 days time – Panagiotis Kanavos Jan 05 '16 at 12:34

1 Answers1

6

You probably want iso_week:

set datefirst 1
select datepart(iso_week,'2015-12-31')    --53
select datepart(iso_week,'2016-01-01')    --53

LiveDemo

EDIT:

Looks like that iso_week is supported from SQL Server 2008+.

Is this possible to avieve this without building dedicated procedure to analyse date and adjust returned value ?

Probably you need to write custom code to calculate it.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • hmm.. that's what I've tried but I've got message: Msg 155, Level 15, State 1, Line 1 'iso_week' is not a recognized datepart option... any idea why? – Rafal_PL Jan 05 '16 at 20:39