"DateDiff" problem with different dtae formats

[Tomeczek]

I have a VB6 program distributed to the company’s system admins around the world. Recently I found out, that under certain circumstances, the program fails with “Type mismatch” error 13.
The error happens when calculating day difference between the date from some database and actual local date.
Writing the program I assumed the date in form MM/DD/YYYY (I’m in US). The date in other part of the World can be in form DD.MM.YYYY or DD/MM/YYYY. When calculating the difference in days:

iDays = DateDiff(“d”, DateTime1, DateTime2)

and having two different date/time formats, I’m getting an error.

Any ideas on how to fix the problem?

 

[gmmastros]

What is the data type of DateTime1 and DateTime2?

If you ONLY keep dates in date variables, you shouldn't have this problem. Specifically, your database should return a DateTime data type, and you should convert any strings (in VB6) to dates using the CDate function. CDate will use regional settings (from the operating system) to convert from string to date.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Tomeczek]

Thank you, George!

I'll try to use CDate, as you suggested.

 

[SkipVought]

CDate() assumes yyyy/mm/dd or mm/dd/yyyy type structure!!!

If your TEXT STRING date represents DAY first, then TILT with any direct conversion function!!!!

You must parse your d, m & y and CONVERT to a date serial NUMBER using the DateSerial(y,m,d) function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gmmastros]

Sorry Skip. I think you are wrong. I just tested this to confirm.

I started a new VB6 project with this code:

Option Explicit

Private Sub Form_Load()

    MsgBox Month(CDate("1/2/2013"))

End Sub

When I run it, I get: 1

I then went in to the regional settings on my computer. My format was set to "English (United States)", so the month was correct because CDate interpreted this as Jan 2.

I then changed the regional settings to "English (United Kingdom)", clicked apply, and then re-ran the VB6 app. This time the message box showed 2, so the date was interpreted as Feb 1.

I tried looking for VB6 specific documentation, but it's becoming harder and harder to find.

This documentation is for VBA:

http://office.microsoft.com/en-us/access-help/type-conversion-functions-HA001229018.aspx

CDate recognizes date formats according to the locale setting of your system. The correct order of day, month, and year may not be determined if it is provided in a format other than one of the recognized date settings. In addition, a long date format is not recognized if it also contains the day-of-the-week string.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Tomeczek]

Thank you, guys!
This discussion helped me a lot.

 

[SkipVought]

George, naturally, you are correct.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gmmastros]

Skip,

There's nothing natural about it. Specifically, I respect you, the advice you give, and the way you do it. Truth is, I wrote my original response based on memory, without investigating/researching it. Your reply made me pause and do some research to make sure that the best advice was given.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Tomeczek]

I still have a problem calculating DateDiff.
Here are more details.

1. Person in Europe picks the date and time of certain process; for example date: “15.6.2013”, time: “13:15”
2. Information is written to the file as two string values: “15.6.2013” and “13:15” (European format)
3. Another person, located in US, reads date and time into string variables strDate and strTime
4. That person tries to find difference in days between “now” and date given above: DateDiff(“d”, Now(), strDate)
5. DateDiff reports error 13 – data mismatch, because of different US date format (“15” is taken as a month)

Do you think that this kind of problem can be solved?

 

[strongm]

Here's your problem:

> 2. Information is written to the file as two string values: “15.6.2013” and “13:15” (European format)

If you have to use strings, I'd use an unambiguous format, such as ISO 8601

 

[gmmastros]

From your original post: "date from some database"

Most databases have a DateTime data type. If you store the data (within the database) as a DateTime instead of string, and you handle the data coming out of the database as a VB6 Date data type, you could probably avoid this problem.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[strongm]

> Person in Europe ... Another person, located in US,

I'd suggest that you have an additional problem to contend with here: time zones.

 

[Tomeczek]

When it comes to Time Zones - I'm good. The date/time is calculated in UCT.

I called it "data base", but it looks more like a log file: flat text file.

 

[gmmastros]

In that case, you should take strongm's suggestion regarding ISO 8601. Information regarding this format can be found here:

http://www.w3.org/TR/NOTE-datetime

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Tomeczek]

Thank you all for your responses.

The ISO 8601 format implemented made the program work! DateDiff takes the date in this format.