Wrong weeknr in sql

Posted on 2005-04-24
Medium Priority
Last Modified: 2010-03-19
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.
Question by:keezebees
LVL 28

Expert Comment

ID: 13853549
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.
LVL 34

Expert Comment

by:Brian Crowe
ID: 13853572
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.
LVL 11

Expert Comment

ID: 13854636
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.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  


Author Comment

ID: 13854638

The value of SELECT @@DATEFIRST is 1

Author Comment

ID: 13854658
Defined by briCrowe ?

Does this mean there is a workaround or do I have to code it myself?
LVL 11

Accepted Solution

lluthien earned 1000 total points
ID: 13854706
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


Author Comment

ID: 13854892
Well, I guess I just will have to code around it.
Points will go to lluthien, Thanks for the tips.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question