How can I convert a Sql Server 2008 DateTimeOffset to a DateTime

Handling conversion

When dealing with conversions from DateTimeOffset to DateTime, consider nuances for maintaining the true chronological point.

Adjusting for timezone offsets

Mind the offset with DateTimeOffset. It's paramount that the DateTime reflects the original DateTimeOffset in UTC:

--Remember we're looking for UTC, not just tea time!
SELECT SWITCHOFFSET(yourDateTimeOffsetColumn, '+00:00') AS UTCDateTimeOffset,
       CONVERT(DateTime2, SWITCHOFFSET(yourDateTimeOffsetColumn, '+00:00')) AS ConvertedDateTime
FROM yourTableName;


SWITCHOFFSET navigates to UTC before casting to DateTime2, preserving high precision.

Using AT TIME ZONE for precision

From SQL Server 2016, the AT TIME ZONE function can convert to UTC:

-- Back to the future!
SELECT yourDateTimeOffsetColumn AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS ConvertedDateTime
FROM yourTableName;


The AT TIME ZONE function brings DateTimeOffset to DateTime, matched to UTC.

Preserving precision with DateTime2

Keep precision by converting to DateTime2 before shifting to DateTime:

-- No time like the present!
SELECT CONVERT(DateTime2, yourDateTimeOffsetColumn) AS DateTime2Format
FROM yourTableName;


With CONVERT use style 126 to keep full precision when changing to DateTime:

-- Smoother than a Swiss watch!
SELECT CONVERT(DateTime2, yourDateTimeOffsetColumn, 126) AS HighPrecisionDateTime
FROM yourTableName;


Understanding local times and UTC

Converting dates and times isn't always as simple as it appears. The nuances of local and UTC times can impact your results.

Handling local time differences

To compare local times, use GETUTCDATE() to get the current UTC time:

-- What time is it Mr. Wolf?
SELECT GETUTCDATE() AS CurrentUTCTime;


Managing server timezone differences

Find the server's timezone offset with DATEPART and tz:

-- Let's not get lost in time
SELECT DATEPART(tz, SYSDATETIMEOFFSET()) AS ServerTimezoneOffset;


Always remember to adjust for server timezone differences to prevent incorrect conversions.

Verifying conversion

Your tests need to be as thorough as a detective novel plot. Use different DateTimeOffset values and consider the quirks of daylight saving and leap seconds:

-- Watson, let the tests begin!


Automate your testing to compare expected and actual DateTime results.