How to properly support English and French dates with VB6 and SQL Server?

Question

We are adding French support to an older VB6 app.

The original developer used a mix of date formats when writing to the database, but SQL didn't seem to mind as it was all in English. (March 31, 2009 or 2009-03-31 etc...)

When you try running the app on a French machine, you get:

[-2147217913][Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime datatype.

I believe the problem is the SQL looks like set xxxx='31-mars-2009' and the SQL Server we have can understand english but not french.

The original fix was to use Format(string,"short date") which seemed to fix it, but I don't think that's fixing it 100% of the time. (Note: I think short date works correctly, there was a text box that had to be converted to a string.)

I played around with SET LANGUAGE and that didn't seem to help (but I may not know what I'm doing.)

I believe the "most correct" solution is to format all dates numeric using YYYYMMDD. Is that correct?

(the app will never need to be ported to any other languages, just English and French.)

Answer 1

There are 2 unambiguous dateformats that you can use with SQL Server YYYYMMDD is one of them. Things can get a little tricky when dealing with other languages. In SQL Server, you can set a different language for each login. It's the login's default language that dictates how dates are interpreted. Unfortunately, the default language may not necessarily correspond to the client computer's regional setting.

This is why I don't allow users to enter dates in to text boxes. Instead, I give them a DatePicker control which does all of my validation for me. The DatePicker control has a .Value property that is a Date datetype. Then, when sending dates to SQL Server, you simply use the Format function (with style set to YYYYMMDD).

For more reading on this: Setting a standard date format for SQL Server