How do I set the DateTimeMode property when filling a DataTable

I am investigating how best to manage UTC timestamps in ADO.Net. We
ran into a lot of issues with DateTime serialization in .NET 1.1. With
..NET 2.0, there are new properties to help identify when a DateTime (as
part of a DataTable or as a regular object) is UTC or local.

I have been able to handle all cases so far except one. In our
database we store all timestamps in UTC. I want to retrieve the data
into a DataTable whose column has the DateTimeMode property set to
DataSetDateTime.Utc. I can't figure out how to do this.

Any suggestions?

I have tried setting the DateTimeMode after the DataTable has been
populated, but that causes an exception. I haven't been able to find
anything in SQL Server 2005 that would allow me to specify that a
DateTime column is a "UTC" DateTime.

Here is my test code. I know it is poor coding, but it is only a test.
For simplicity, I just use a raw SQL statement.


SqlConnection conn = new
SqlConnection(@"server=(local);database=master;uid=sa;pwd=password;");

SqlCommand cmd = new SqlCommand("SELECT GetDate() AS local,
GetUTCDate() AS utc", conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("TryIt");

try
{
adapter.Fill(dt);
dt.Columns["utc"].DateTimeMode = DataSetDateTime.Utc;
dt.Columns["local"].DateTimeMode = DataSetDateTime.Local;
}
catch (Exception)
{

throw;
}

DateTime local = (DateTime)dt.Rows[0]["local"];
DateTime utc = (DateTime)dt.Rows[0]["utc"];

.

Relevant Pages

  • Re: UTC dates in SQL 2000
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... > This db provides dates, which the developer > identifies as UTC, in the format 99999. ... > getutcdatefunction to return the datetime for the> current UTC, or I can CONVERTto convert a> datetime into UTC, but neither will work in an ActiveX> script. ...
    (microsoft.public.sqlserver.dts)
  • Re: ToFileTime vs ToFileTimeUtc issues
    ... "Previous versions of the ToFileTime method assume the current DateTime ... DateTime object is a local time, a UTC time, or an unspecified kind of time ... .", dt, ftLastWriteTime); ... System.DateTime dtLastWriteTimeUtc; ...
    (microsoft.public.dotnet.framework)
  • Re: Web services and incorrect handling of time zones in DateTime
    ... You are not the first one writing this, I think that it is an error by design. ... when the SOAP message contains datetime like ... exactly the same time as previously, but in UTC), it becomes DateTime ... the UTC time should be converted to local time ...
    (microsoft.public.dotnet.general)
  • How do I set the DateTimeMode property when filling a DataTable
    ... I am investigating how best to manage UTC timestamps in ADO.Net. ... ran into a lot of issues with UTC DateTime serialization in .NET 1.1. ... Here is my test code. ... SqlConnection conn = new ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Time zone
    ... Always get, set, calc, and store only UTC ... Your client side ... can easily convert to/from UTC so they can display local times. ... change their time zone when they travel and still get and use the datetime ...
    (microsoft.public.dotnet.languages.csharp)