How do I ensure that all clients can correctly parse a database DateTime string?

Question

I got a Windows application, used on hundreds of computers. It gets dates from my Sql Server as string and converts this string to datetime format to calculate some date time difference. Sometimes on some system, we get error while converting string to Date. "String was not recognized as a valid DateTime".

Every computer's Windows DateTime settings are not the same and we cant even force client to use one format.

Now how to handle this at code side?

Answer 1

Assuming your dates are stored in a consistent format in your SQL table, you can use DateTime.Parse with an explicit format string to convert the string to a datetime. See: http://msdn.microsoft.com/en-us/library/system.datetime.parseexact.aspx

Answer 2

You would need to know what culture the date strings are created as, given that you can use the DateTime.Parse-method as follows:

DateTime.Parse("2001-01-01", CultureInfo.GetCultureInfo("sv-SE"));

Where the string is the date from the database and the culture is the known culture.

Answer 3

Change the string format that SQL Server returns. If you use a YYYY-MM-DD format for the date, conversion should work fine regardless of the settings.

Also, with some luck, you will have used stored procedures and don't even have to update your software.

Why are you returning the datetime as a string however, ADO.NET can handle this very well?

Answer 4

Use a DateTime datatype on the client and the server, if you can, and you'll completely avoid these problems. There should be no need to store the date as a string at any point.

If this is too big of a change to make in your application, explicitly specify the format of the date string in your DateTime.ParseExact call:

string databaseDateFormat = "yyyy-MM-dd HH:mm:ss.fff";
DateTime myDate = DateTime.ParseExact(myDateString,
                                      databaseDateFormat,
                                      CultureInfo.InvariantCulture);