Problem with DateDiff and English (Canada) Regional Settings.

Posted on 2007-10-01
Last Modified: 2010-08-05
Hello Everyone,
I'm trying to get the age of a person in one of our legacy apps, but I'm not getting the same results between English (US) and English (Canada) regional settings. I'm using VB6 SP6 on Windows Server 2003 SP2.
Here's the sample code:
    Dim dtBirth As Date            'person's birth date
    Dim dtReport As Date      'report date
    dim lngDays as long            'number of days
    Dim lngYears as long      

    dtBirth = #12/4/1985#
    dtReport = #9/28/2007#

    'get number of days between dates
    lngDays = DateDiff("d", dtBirth , dtReport )
    'determine number of years
    lngYears = (lngDays / 365.25)
    With English US I'm getting the proper number, 21. With English Canada I'm getting 22.
Any help would be appreciated. Thanks
Question by:mwDev
    LVL 6

    Expert Comment


    I believe the reason is that in your example, the DOB is being interpreted as DD/MM/YYYY for English Canada... making them 22.

    You'd have to account for this change in your code.

    - M
    LVL 42

    Expert Comment

    I think this will give you the same answer in both cases:

    lngYears = int(lngDays / 365.25))

    Having said that, it's still  not 100% accurate.

    LVL 42

    Expert Comment

    Try this:
        Dim dtBirth As Date            'person's birth date
        Dim dtReport As Date      'report date
        Dim lngYears as int  

        dtBirth = #12/4/1985#
        dtReport = #9/28/2007#

        'get number of years between dates
        lngYears = DateDiff("d", dtBirth , dtReport )
        if DatePart("y",dtReport) < DatePart("y",dtBirth) then
          lngYears = lngYears - 1
       end if

    Author Comment

    I my previous posting to duplicate the problem with the English (Canada) settings the dates must be
    dtBirth = #4/12/1985#
    dtReport = #28/9/2007#

    I believe the datediff is inverting the mongth and day when in Engilsh (Canada).
    LVL 76

    Accepted Solution

    The hash format is defined to be the American format - #mm/dd/yyyy#, so the date that it finds should be independent of the regional setting.

    Whatever the setting, (US, UK or Canada), my system always returns 7968 days which, when divided so, always gives 22 (right or wrong), as does

     lngyears = DateDiff("yyyy", dtBirth , dtReport )

    To the first two decimal places, the calculation is 21.82, so 22 is more accurate than 21. If you require the number of complete years, use the formula that dqmq provided in his/her comment.
    LVL 26

    Expert Comment

    may be use fix?

    lngYears = Fix(lngDays / 365.25)

    Author Comment

    In theory the results should be the same regardless of regional settings. When I use English (Canada) I get 7968 days, however when I switch to English (US) I get 8204 days. Which is the different when the dates are inverted.
    LVL 26

    Expert Comment

    8204 is getting from  April 12, 1985, instead of Dec 4, 1985

    You may want to extract the date and then use dateserial to compose the date before checking the diff

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Extract Data and filename Macro 20 90
    DIR issue 7 38
    Best book to learn C++ 3 31
    Copy a row 12 19
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    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…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    729 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

    20 Experts available now in Live!

    Get 1:1 Help Now