Wrong weeknr in sql

Hi there,

Why is it that my calender says this week is nr 16 and this code:

SELECT ClientHost, SUM(AantalHits) AS Expr1, DATEPART(week, Datum) AS Maand
FROM  dbo.AantalAanlogPerIp
GROUP BY ClientHost, DATEPART(week, Datum)

says it's weeknr 17

Is it because this year starts with week 53 the first 2 days (1 and 2 Jan) ?

bytheway My system country is Dutch. (The Netherlands)

Thanks Kees.
Who is Participating?
lluthienConnect With a Mentor Commented:
what rafrancisco said is important as well tho.
the default value of 7 doesn't work for dutch settings,
in holland the first day of the week is monday (1)

so you should set that to 1 for sure.
still, if i recollect correctly.

you will still have a difference in weeknumbers when january 1 is on a thursday or friday.
so you will have to either correct this, or not use weeknumbers from sql server.
in the latter case, you would just return the date instead of the week
and change from date to week in the receiving end of your query.

see here for reference: http://www.tondering.dk/claus/cal/node7.html#sec-firstDay

According to Books Online:

"The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year."

To know what is currently set as your DATEFIRST, do this:


@@DATEFIRST returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.   The U.S. English default is 7, Sunday.

So, check what is the value of your @@DATEFIRST, which can be the reason why you are getting a different value.
Brian CroweDatabase AdministratorCommented:
By definition the first week of the year is the one that contains Jan 1.  If you want something other than this then you will have to offset it yourself.
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Hi kees,

in holland the used system for week 1 of a year is determined as:
the first week of the year with more than 3 days.

that's why week 1 this year starts on january 3.
the defnition for week 1 of sql-server is as defined by briCrowe.

that would explain the difference.
keezebeesAuthor Commented:

The value of SELECT @@DATEFIRST is 1
keezebeesAuthor Commented:
Defined by briCrowe ?

Does this mean there is a workaround or do I have to code it myself?
keezebeesAuthor Commented:
Well, I guess I just will have to code around it.
Points will go to lluthien, Thanks for the tips.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.