SQL Server 2005 Express cannot update datetime field when windows is set to Italian

Question

Hi,

I am struggling with an MS SQL 2005 Express issue. A query like the one below works perfect when Windows Regional Options (control panel) is set to English (United States), but it fails when set to Italian (and maybe other languages).

UPDATE MyTable SET StartDate='2009-07-14 12:05:30' WHERE ID=7

I tried also many different date-time formats such as ISO 8601:

yyyy-MM-ddTHH:mm:ss

(see also: http://www.karaszi.com/SQLServer/info_datetime.asp)

None of these formats work except for yyyyMMdd, but then I loose the time which is not acceptable.

Also executing this query first makes no difference:

SET LANGUAGE us_english

PS: I execute the query from a .Net program, but SQL Server Management Studio gives the same result: it cannot convert the string into a data-time. As soon as the settings are changed back to US English it works again.

How do I make this query work with other language settings?

Answer 1

You should use region-independent dates. In SQL Server, you can do this:

UPDATE MyTable SET StartDate={ts '2009-07-14 12:05:30'} WHERE ID=7

From code, it's even better is to use named parameters and pass a DateTime object as parameter.

Answer 2

Did you try yyyymmdd hh:mm:ss format?

UPDATE MyTable SET StartDate='20090714 12:05:30' WHERE ID=7

Answer 3

After some hours of trying I found the solution. The ISO 8601 format works perfect in MS SQL, but something went wrong in formatting the date in VB.net.

First I used the following format specifier:

Dim dateString As String = aDate.ToString("yyyy-MM-ddTHH:mm:ss")

However, somehow the result is something like "2009-07-14T12.05.30" instead of "2009-07-14T12:05:30", so dots instead of ":"!

When using

Dim dateString As String = aDate.ToString("s")

The correct format is applied: "2009-07-14T12:05:30".