Locales and date format in SQL

I am working on a VB.NET project. The program is a Windows service that connects to an MS Access database. Everything worked fine on my own pc, but on another pc, we ran into some problems. It appeared to be related to regional settings. By default, the service ran as the LocalSystem account. I don’t know how to change the settings for that account, (anyone?) so for testing we ran the service under different user accounts. And indeed, for a user with “en-US”-settings, everything worked fine, but for a user with “nl-NL”-settings, we got weird results.

First of all, I found out that you have to be careful with floating point numbers in your SQL-statements.

(en-US)
UPDATE TableX SET FieldY = 3.1415

works fine, but

(nl-NL)
UPDATE TableX SET FieldY = 3,1415

results in an ODBC error.

Ok, that’s fair. And it can easily be overcome by using a different NumberFormat

Dim nfi As System.Globalization.NumberFormatInfo = New System.Globalization.CultureInfo("en-US").NumberFormat
Single.ToString(nfi)

or by setting the CurrentCulture of the CurrentThread

System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

However, I keep running into trouble with dates in my queries.
I have a table with date and time values, and I want to retrieve records for a certain date. So I want to use a query like

SELECT * FROM TableX WHERE Format(Datefield, "yyyy/mm/dd") = '2007/01/23'

But no matter what I do, and how often I set the CurrentCulture, this query doesn’t work if the service runs as a user with Dutch settings.
As a test, I ran the following query (with Dutch settings):

SELECT Format(Datefield, "yyyy/mm/dd") FROM TableX

and it returns “2007-01-23” instead of “2007/01/23”.

It seems as if the OdbcConnection, or the ODBC driver, or something else (?) still uses the Dutch settings, although my CurrentThread uses “en-US”-settings.

Can anyone explain this? Any ideas about how to solve this? Clearly, I want my program to work independently of the regional settings!

For now, I have solved it using

Format(Datefield, "yyyymmdd")

but surely it must be possible some other way?

Tags: , , , ,

Tuesday, January 23rd, 2007 Computing

2 Comments to Locales and date format in SQL

  1. You are using Access as a backend. Access is primarily a desktop application and always (not sure, but never seen it otherwise) runs on the client’s pc. therefore it will always use the desktopsettings even if the actual db is on a server.

    for the best use of dates in Access check: http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html
    (about halfway through the page is a bit about dates)

    This is probably not what you want to hear, but the use of SQL Server (or any other SQL-flavor on a server) most likely solves this problem.

  2. monzo on February 1st, 2007
  3. Of course, I know the difference between Access and SQL Server. And I know Access runs on the client pc and uses those settings.

    That’s exactly why I always compare dates using the Format() function: to make sure that the date field from the table and the date literal I want to compare with, are in the same format.

    But apparently that is not the case!
    Even if you use Format(datefield, “yyyy/mm/dd”) with Dutch settings, it returns “2007-02-01”. And I have not yet found a way to change these settings. As I already wrote, setting the CurrentCulture didn’t help…

  4. Benjamin on February 1st, 2007

Leave a comment