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

datetimeoffset

What we started with and want to end with.

timezoneoffset

What the DatePart extracted from the original datetimeoffset

rebuilt

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:15 735 2007-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:

WordPress.com Logo

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

Google+ photo

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

Twitter picture

You are commenting using your Twitter 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.