ISO datetimes transmitted as strings to localized Sql Server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

adi_lazar - 18 Oct 2007 09:36 GMT

I have an web app in .net 2.0 that is using datetime and this is displayed
and can be input in the current locale( different from country to country:
Germany = MM.dd.yyyy, US, french or australian).
I need to update sql server datetimes with the correct values.
The Sql Server could be localized in another country (ex. web app in Germany
and Sql server in France).

What is the correct approach to cover all the possible datetime formating
differences ?

I read about ISO 8601 dates, but when I tried to use the following Sql
statement:
string cmd = string.Format("UPDATE myTable1 SET datetime1 = '{0}'",
myLocalDate.ToString("yyyy-MM-ddTHH:mm:ss.fffff",
CultureInfo.InvariantCulture));
I'm getting an error on sql server about bad format of char trying to cast
to datetime !!??

What am I doing wrong ?

Thnx for any advice

Reply to this Message

Mihai N. - 19 Oct 2007 09:03 GMT

> I read about ISO 8601 dates, but when I tried to use the following Sql
> statement:
[quoted text clipped - 5 lines]
>
> What am I doing wrong ?

I don't know what are you doing wrong, but a locale-independent date format
(iso 8601 is perfect) is the right thing to do.
Maybe you also have to configure the sql server to accept dates in that
format?
You might want to check if the SQL server has something like
SET DateStyle TO 'ISO' (PostgreSQL)
MS SQL 2000 (I don't know about newer versions) did not directly
support ISO 8601, but you could use SET DATEFORMAT MDY and it would
accept the string you are building (YYYY-MM-DDTHH:MM:SS.fff)

Signature

Mihai Nita [Microsoft MVP, Windows - SDK]
http://www.mihai-nita.net
------------------------------------------
Replace _year_ with _ to get the real email

Reply to this Message

Rate this thread:

ISO datetimes transmitted as strings to localized Sql Server

Free Magazines