Update Only TZoffset in DATETIME column-CodePudding

I have a set of dates like 2023-11-30 23:59:59.0000000 00:00. I want to update only the time zone offset from 00:00 to 01:00 to all of them. If it makes things easier, all of the times are 23:59:59.0000000 and only the date differentiates.

I've tried this:

UPDATE Table SET Date = DATEADD(DAY, DATEDIFF(DAY, 0, Date), '2023-11-30 23:59:59.0000000  01:00')

But it only worked with accuracy up to seconds, without milliseconds, nanoseconds and tzoffset. I've got this error, when I tried that:

Conversion failed when converting date and/or time from character string.

CodePudding user response：

Seems like you want TODATETIMEOFFSET:

DECLARE @YourValue datetimeoffset(7) = '2023-11-30 23:59:59.0000000  00:00';

SELECT TODATETIMEOFFSET(@YourValue,' 01:00');

This returns 2023-11-30 23:59:59.0000000 01:00. This works because it takes your datetimeoffset value and (implicitly) converts it to a datetime2 first, which truncates the offset value, and then adds the new offset to the value; thus not changing the actual time and just the offset.

If you're saying that you actually change the value so that the date and time changes with the offset, then you want SWITCHOFFSET:

DECLARE @YourValue datetimeoffset(7) = '2023-11-30 23:59:59.0000000  00:00';

SELECT SWITCHOFFSET(@YourValue,' 01:00');

This returns 2023-12-01 00:59:59.0000000 01:00.