Add hour to all DateTimeOffsets in SQL Server

Question

I have timestamps stored in a DateTimeOffset datatype in a table. I want to add 1 hour to all of these dates&times. What is the most efficient way to do this, preferably with regular SQL?

I don't want to change the offset, just add 1 hour. So the following timestamp:

2018-04-26 14:46:25.4089 +01:00

becomes

2018-04-26 15:46:25.4089 +01:00

Potentially I will be updating over 1 million rows in this manner, so it needs to be efficient.

Check out [this SO post](https://stackoverflow.com/questions/39419529/best-way-to-update-40-million-rows-in-batch) - it's not a duplicate but it does talk about updating a large number of records. — Zohar Peled, Apr 29 '18 at 13:09

score 1 · Accepted Answer · answered May 01 '18 at 10:36

For 1 million rows, you can simply do this (it should be done in about one minute):

UPDATE YourTable SET ColumnName=DATEADD(HOUR,1,ColumnName)

For more rows, you can do it in batches of 10000 rows (or 100000 rows), but you need an additional column to keep track of what you already updated.

If you already have an int column in the table (for example an IDENTITY column), you can do something like this (in AdventureWorks):

DECLARE @x INT=(
    SELECT MIN(SalesOrderDetailID/10000)
    FROM Sales.SalesOrderDetail
)
WHILE EXISTS (
    SELECT * FROM Sales.SalesOrderDetail
    WHERE SalesOrderDetailID/10000>=@x
) BEGIN
    UPDATE Sales.SalesOrderDetail
    SET ModifiedDate=DATEADD(HOUR,1,ModifiedDate)
    WHERE SalesOrderDetailID/10000=@x
    SET @x=@x+1
END

If you may add an additional column, you can also do something like this:

ALTER TABLE Sales.SalesOrderDetail ADD WasUpdated BIT NOT NULL
    CONSTRAINT DF_SalesOrderDetail_WasUpdated DEFAULT (0)
GO
WHILE 1=1 BEGIN
    UPDATE TOP (10000) Sales.SalesOrderDetail
    SET ModifiedDate=DATEADD(HOUR,1,ModifiedDate), WasUpdated=1
    WHERE WasUpdated=0
    IF @@ROWCOUNT=0 BREAK
END
GO
ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT DF_SalesOrderDetail_WasUpdated
ALTER TABLE Sales.SalesOrderDetail DROP COLUMN WasUpdated

Add hour to all DateTimeOffsets in SQL Server

1 Answers1