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.
>Solution :
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.