How do I update a column's offset in SQL Server?

Question

I converted a table's DateTime field to DateTimeOffset, but now the offset is automatically set to +00:00.

I need to change all DateTimeOffset fields of this table to an offset of +1:00.

How can I do this in an update query?

Answer 1

You can use SWITCHOFFSET to change the offset. You will need to subtract the amount of hours though from the date if you don't want the date to change.

SELECT  SWITCHOFFSET(DATEADD(hh, -1, CAST (GETDATE() AS DATETIMEOFFSET)),
                         '+01:00')

Answer 2

You can use TODATETIMEOFFSET(datetime, '+01:00' ) This wont affect the datetime part.

Answer 3

DECLARE @t DATETIMEOFFSET

SELECT @t = Getdate()

SELECT Replace(@t, RIGHT(@t, 6), '+01:00') 

<update tablename set offsetfield = Replace(offsetfield, RIGHT(offsetfield, 6), '+01:00')>