More on T-SQL DateTimeOffset, DatePart,ToDateTimeOffset, SwitchOffset and Timezones


SELECT CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(3)) AS 'datetimeoffset'
,DATEPART(tz,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(3))) AS 'timezoneoffset'
,TODATETIMEOFFSET(CAST('2007-05-08 12:35:29.1234567' as datetimeoffset(3)), 735) as rebuilt


What we started with and want to end with.


What the DatePart extracted from the original datetimeoffset


What happened when we put the original date back in, without the timezone, but added the timezone later.

2007-05-08 12:35:29.123 +12:157352007-05-08 12:35:29.123 +12:15

Notice that the ending result is the same as the starting set. This is a success. Where as if you would have used SwitchOffset, the time would have changed, because it would assume that your original time was UTC, and you wanted to look at it in another timezone.

From this you should have learned a little more about DatePart, and ToDateTimeOffset.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.