DatePart("ww", "03/22/2009") Returns it as Week #13 when it should be #12?

Posted on 2009-04-16
Last Modified: 2012-05-06
Why does
DatePart("ww", "03/22/2009")
Return it as Week #13 when it should be #12?
The first day of the week is defaulted to Sunday...which should still return 12, not 13.

Does anyone have any ideas on why.
Question by:16spam16
    LVL 92

    Accepted Solution

    Hello 16spam16,

    The optional 4th argument in DatePart determines how to define the first "week" of the year.  Using this in
    the Immediate window gets 12:

    ?DatePart("ww", "03/22/2009", vbSunday, vbFirstFullWeek)


    LVL 1

    Author Comment

    Now that did work, but then i changed it and tried this day. and it was wrong...

    DatePart("ww", "03/16/2008", vbSunday, vbFirstFullWeek)

    returns 11, when it should return 12.

    I changed it to

    DatePart("ww", "03/16/2008", vbSunday, vbFirstFourDays) returned 12
    DatePart("ww", "03/22/2008", vbSunday, vbFirstFourDays) returned 12

    Is that correct? the answers are correct.

    What I am doing is selecting the week via the VB6 Calendar control with my weeks starting on Sunday.
    Then I use DatePart to get the week number when i do my processing. It is funny on I get different week numbers.

    What are your thoughts?
    LVL 92

    Expert Comment

    by:Patrick Matthews
    From the VBA help (works the same way in VB6), these are the values you can use:

    Constant      Value      Description
    vbUseSystem      0      Use the NLS API setting.
    vbFirstJan1       1      Start with week in which January 1 occurs (default).
    vbFirstFourDays      2      Start with the first week that has at least four days in the new year.
    vbFirstFullWeek      3      Start with first full week of the year.

    See which definition matches best to your expectations.

    16spam16 said:
    >>What are your thoughts?

    My thought is that week numbers are usually a waste of time, because everyone has a different idea of how to
    define them :)

    Seriously, if none of these definitions meet your needs, then you will need to use a calendar table or something
    to explicitly define your week numbers.
    LVL 1

    Author Comment

    I have looked through the VBA help and i couldnt find anything that says how the "MonthView" control figures out week numbers. I think if i knew that id know what to pick.

    I have run a few more dates and it seems as though

    DatePart("ww", mydate, vbSunday, vbFirstFourDays)

    seems to have done the trick. atleast it appears to anyways.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    745 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

    17 Experts available now in Live!

    Get 1:1 Help Now