.NET Store DateTime in SQL in different time zones and in different language versions
What is the best way to store DateTime in SQL for different timezones and different locales
There are a few questions / answers about timezones, but none of them address locale issues. DateTime.ToUniversalTime is locale specific and I need it regardless of locale.
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 store DateTime in France Locale in SQL SQL DB, you get an error because it is the wrong date format.
Your best bet would be a C # code snippet for
- Get DateTime in specific locale and store it in SQL datetime field
- Received SQL datetime field and converted to DateTime locale
thank
source to share
It is not clear to me what you want to achieve, maybe you should look at the DateTimeOffset class. This gives you control over your UTC time offset, and you can be "locale independent" this way. You can save your time in the database in UTC format and then convert it however you want. The question is how do you want to transform it.
source to share
I prefer that all the time passing in UTC and coming out belongs to the user. This way, no matter where the server is located, the application is irrelevant. The hard part is changing the time zone offset, for example in all different time zones and different daylight saving rules, etc. For this, I am using the System.TimeZoneInfo class in 3.5.
EDIT: Here's a snippet of a project I'm currently working on:
- user time - = TimeZoneInfo.ConvertTimeFromUtc ((DateTime) sg.GameTimeUTC, s.User.TimeZoneInfo);
I am getting the user's local time using the TimeZoneInfo static ConvertTimeFromUtc and passing it the Utc time that I am converting as well as the user's TimeZoneInfo.Id value (string) that I store in the user profile. The ConvertTimeFromUtc method takes care of all the ugly details regarding all time zones and their rules.
source to share
If you just store DateTime
in UTC in the database this shouldn't be a problem and the below 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;
}
}
}
source to share