tsql datetimeoffset

Question

in the following example

1912-10-25 12:24:32.000 +10:00 << may I know 1912-10-25 12:24:32.000 is UTC time or

1912-10-25 02:24:32.000 is the UTC time ???

Answer 1

The +10:00 indicates the time "12:24:32" is +10:00 hours offset from UTC time. UTC time would be 12:24:32 - 10:00:00 = 02:24:32 UTC

See:
https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver15#time-zone-offset

Answer 2

Hi @sakuraime

1912-10-25 12:24:32.000 +10:00 << may I know 1912-10-25 12:24:32.000 is UTC time or 1912-10-25 02:24:32.000 is the UTC time ???

A time zone offset specifies the zone offset from UTC for a time or datetime value. The time zone offset can be represented as [+|-] hh:mm.
In this example, it is +10:00, which means the time zone offset is added 10 hours from the UTC time. Therefore 1912-10-25 02:24:32.000 should be the UTC time.

If you are working with SQL Server 2016 (13.x) and later,then you could use AT TIME ZONE to converts date to a specified time zone considering DST (daylight saving time) changes.
In addition, you could also use the function SWITCHOFFSET to move it from one timezone to another, but still keeping the same UTC value.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi,

You can convert the time using "AT TIME ZONE"

For example:

DECLARE @datetimeoffset datetimeoffset(3) = '1912-10-25 12:24:32 +10:0'; -- Time in UTC+10   
select @datetimeoffset AT TIME ZONE 'UTC' -- Time in UTC  

You can read more about how to use "AT TIME ZONE" in this document: https://learn.microsoft.com/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin