.NET Store DateTime in SQL in different timezone and different locale

Question

What is the best way to store DateTime in SQL for different timezones and different locales
There a few questions/answers about timezones, but none is addressing the locale problems. DateTime.ToUniversalTime is locale specific, and I need it locale independent.

For example:

  DateTime.Now.ToUniversalTime.ToString()
        In US locale returns something like: 11/23/2008 8:20:00   
        In France locale returns   23/11/2008 8:20:00 

Notice that day/month are inverted

If you save the DateTime while in France Locale on a US SQL DB - you get an error, because it is a wrong date format.

Best would be a C# code snippet for

1. Get a DateTime in a specific locale and store it in SQL datetime field
2. Retrieved a SQL datetime field and convert it to a locale DateTime

Thanks

Answer 1

It is unclear to me what you want to achieve, maybe you should take a look at DateTimeOffset class. This gives you control over your time offset relative to UTC and you can be 'locale independent' this way. You can store your time in the database in UTC and then convert it however you want. The question is how you want to convert it.

Answer 2

I prefer that all time going in be UTC and coming out be user-relative. That way, whatever server location the app runs on becomes irrelevant. The tough part is when the timezone offsets change - eg., all the different time zones and different rules for daylight savings, etc. For that, I use the System.TimeZoneInfo class in 3.5.

http://csharpfeeds.com/post/5336/Exploring_Windows_Time_Zones_with_System.TimeZoneInfo_Josh_Free.aspx

EDIT: Here's a snippet from a project I'm working on now:

--the user-relative time-- = TimeZoneInfo.ConvertTimeFromUtc((DateTime)sg.GameTimeUTC, s.User.TimeZoneInfo);

I get the user's local time by using TimeZoneInfo's static ConvertTimeFromUtc and passing it the Utc time I'm converting from as well as the user's TimeZoneInfo.Id value (a string) which I store in the user's profile. The ConvertTimeFromUtc method takes care of all the ugly details regarding all the different time zones and their rules.

Answer 3

If you just save the DateTime in UTC in the database, this should not be an issue and the following should work IMO.

namespace MyNamespace
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;

    using System.Globalization;

    using System.Threading;

    public sealed class MyProgram
    {
        private DbConnectionStringBuilder connectionStringBuilder;

        public static void Main(string[] args)
        {
            DbConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(args[0]);
            MyProgram myProgram = new MyProgram(connectionStringBuilder);
            myProgram.Run();
        }

        public MyProgram(DbConnectionStringBuilder connectionStringBuilder)
        {
            if (null == connectionStringBuilder)
            {
                throw new ArgumentNullException("connectionStringBuilder");
            }

            this.connectionStringBuilder = connectionStringBuilder;
        }

        public void Run()
        {
            IList<Guid> guids = new List<Guid>(2);
            guids.Add(this.Create(DateTime.Now));
            Thread.Sleep(new TimeSpan(0, 0, 5)); // I just want to assure there is a different time in the next row. :)
            guids.Add(this.Create(DateTime.UtcNow));

            foreach(Guid guid in guids)
            {
                Console.WriteLine(this.Retrieve(guid));
            }
        }

        private Guid Create(DateTime dateTime)
        {
            Guid result = Guid.Empty;



            if (dateTime.Kind == DateTimeKind.Unspecified)

            {

                throw new ArgumentException("I cannot work with unspecified DateTimeKinds.", "dateTime");

            }

            else if (dateTime.Kind == DateTimeKind.Local)

            {

                dateTime = dateTime.ToUniversalTime();

            }

            using (IDbConnection connection = new SqlConnection(this.connectionStringBuilder.ConnectionString))
            {
                using (IDbCommand command = connection.CreateCommand())
                {

                    command.CommandText = "INSERT INTO MyTable (MyUtcDate) OUTPUT INSERTED.Id VALUES (@DateTime)";

                    IDataParameter parameter = command.CreateParameter();
                    parameter.ParameterName = "DateTime";
                    parameter.Value = dateTime;
                    command.Parameters.Add(parameter);

                    command.Connection.Open();

                    result = (Guid)command.ExecuteScalar();
                }
            }

            return result;
        }

        private string Retrieve(Guid id)
        {

            string result = string.Empty;

            using (IDbConnection connection = new SqlConnection(this.connectionStringBuilder.ConnectionString))
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT MyUtcDate FROM MyTable WHERE Id = @Id";

                    IDataParameter parameter = command.CreateParameter();
                    parameter.ParameterName = "Id";
                    parameter.Value = id;
                    command.Parameters.Add(parameter);

                    command.Connection.Open();
                    using (IDataReader dataReader = command.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (dataReader.Read())
                        {
                            DateTime myDate = DateTime.SpecifyKind(dataReader.GetDateTime(dataReader.GetOrdinal("myUtcDate")), DateTimeKind.Utc);

                            result = string.Format(CultureInfo.CurrentCulture, "{0}: {1}, {2}: {3}", TimeZoneInfo.Utc.StandardName, myDate, TimeZoneInfo.Local.StandardName, myDate.ToLocalTime());
                        }
                    }
                }
            }



            return result;
        }
    }
}