Wrong weeknr in sql

Posted on 2005-04-24
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

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

    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.

    Author Comment


    The value of SELECT @@DATEFIRST is 1

    Author Comment

    Defined by briCrowe ?

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

    Accepted Solution

    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:


    Author Comment

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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now