Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 679
Fred Fickleberry III Avatar asked Nov 25 '15 09:11

Fred Fickleberry III


People also ask

How do you update a record in SQL?

To get the last updated record in SQL Server: We can write trigger (which automatically fires) i.e. whenever there is a change (update) that occurs on a row, the “lastupdatedby” column value should get updated by the current timestamp.

Can we update NULL value in SQL?

Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them.

How do I change the primary key value in SQL Server?

To modify a primary key Open the Table Designer for the table whose primary key you want to modify, right-click in the Table Designer, and choose Indexes/Keys from the shortcut menu. In the Indexes/Keys dialog box, select the primary key index from the Selected Primary/Unique Key or Index list.


2 Answers

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')
like image 70
Jesse Petronio Avatar answered Sep 19 '22 15:09

Jesse Petronio


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

like image 28
bas boek Avatar answered Sep 21 '22 15:09

bas boek