asked on

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.

rafrancisco

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:

SELECT @@DATEFIRST

@@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 Crowe

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.

lluthien

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.

keezebees

ASKER

Rafrancisco,

The value of SELECT @@DATEFIRST is 1

keezebees

ASKER

Defined by briCrowe ?

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

ASKER CERTIFIED SOLUTION

lluthien

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keezebees

ASKER

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