Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 618
  • Last Modified:

Problem with DateDiff and English (Canada) Regional Settings.

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
0
mwDev
Asked:
mwDev
  • 2
  • 2
  • 2
  • +2
1 Solution
 
MELeBlancCommented:

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
0
 
dqmqCommented:
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.

 
0
 
dqmqCommented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

I believe the datediff is inverting the mongth and day when in Engilsh (Canada).
0
 
GrahamSkanRetiredCommented:
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.
0
 
EDDYKTCommented:
may be use fix?

lngYears = Fix(lngDays / 365.25)
0
 
mwDevAuthor Commented:
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.
0
 
EDDYKTCommented:
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now