CODEWITHSUNDEEP
c#sql-server-2005datetimeoffset
John
John

Reputation: 3392

How do you store DateTimeOffset in a SQL Server DateTime field

This is in C#

I'm consuming data from an RSS feed and want to store the data in our database. The problem that I'm having is how to store the LastUpdateTime.

XmlReader reader = XmlReader.Create(uri);
SyndicationFeed rssFeed = SyndicationFeed.Load(reader);

var lastUpdate = rssFeed.LastUpdatedTime;

The problem is that rssFeed.LastUpdateTime is a DateTimeOffset. However, we're using SQL Server 2005, which only has DateTime. What is the best practice for handling this scenario? If it matters, all our databases and servers are on Eastern time.

Thanks.

Upvotes: 0

Views: 3531

Answers (2)

PapillonUK
PapillonUK

Reputation: 652

It would be simplest to just convert the DateTimeOffset to a UTC DateTime using rssFeed.LastUpdateTime.UtcDateTime and then store just that in the database in a DateTime field.

Don't bother storing the offset, there's no need. The Offset will not relate the DateTime value to any time zone anyway.

You can then use the built in TimeZoneInfo class in .NET to convert your UTC DateTime values to ANY time zone whatsoever. The beauty of the TimeZoneInfo class is that it will also adjust your DateTime values for Daylight Saving Time.

This would also be the best solution in terms of performance and storage.

Upvotes: 2

Phil
Phil

Reputation: 2365

I normally have an additional column, something like

[LastUpdateOffset] SMALLINT NOT NULL

and use that to store the offset for the given row. Then convert for the client, something like

/// <summary>
/// Convert a given UTC DateTime value into its localized value from given offset
/// </summary>
/// <param name="column">DateTime value, such as CreatedOn, UpdatedOn etc</param>
/// <param name="offset">-60 is DST for example</param>
private void ConvertUTCToClientTimeZone(DateTime column, int offset)
{
    TimeSpan offsetTimeSpan = new TimeSpan(0, offset, 0).Negate();
    DateTimeOffset newDate = new DateTimeOffset(column, offsetTimeSpan);
    column = newDate.DateTime;
}

Hope this helps! :)

Upvotes: 1

Related Questions