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?

Shillelagh

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')

Sejant

Date and time should stay the same -- only the offset needs to change. Is this how I do it then? – Shillelagh

Answer 2

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

Muddlehead

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')>

Jolda