Inserting localized / regional setting specific data into sql serv

[Niketa Mahana]

Hi,

We are in the process of making product in windows forms that is localized
presently for all european countries.We are supposed to support all eastern
and western european date , time and number formats.However we are currently
facing a problem that say a user in Italy uses our product and user's
settings have a "." as a time seperator sql server is unable to insert such
time values into the database.We get an error for date-time format is not
recognizable by sql server.This also gives us a problem if the date and time
seperator are both "." .Since several countries in europe do support such
settings we are in a fix.The minute any C# code at the GUI level encounters a
"." date/time seperator the Convert.ToDateTime functions fail and we are
unable to go ahead.
While doing some research for this problem we came up with a solution that
using CultureInfo.InvariantCulture to change all our date-time values before
we pass them onto Convert.ToDateTime functions . We have found this works
however what we dont understand is that we use the 120 datetime format while
inserting data into sql server, this format expects yyyy-mm-dd hh:mm:ss,
which is not what CultureInfo.InvariantCulture expects.But the queries work
perfectly inspite of that.What we are looking for is an explanation for this
since we cannot afford to release yet more patches and releases for our
product once we fix this problem we want the solution to be permanent.
Please guide us as to if the above is a solution to our problem and what is
the explanation for it, or what can be a solution to our problem.

With deepest thanks,
Niketa Mahana
Associate Consultant
Siemens Information Systems Ltd.