CODEWITHSUNDEEP
sqlsql-server
Joel
Joel

Reputation: 6183

convert existing local datetime entries to UTC

I have a bunch of datetime entries in my database that now has to be migrated to UTC.

I've tried something like this to verify the time-difference: 

SELECT CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,GetUTCDate()),'+02:00')) Time_In_Sweden

Local (+2h) to UTC (+0) => i.e. I have to remove 2h.

But i'm not sure how i would go about when executing this query. Is there better ways of doing this?

The datetime column is of type datetime2(7) and called DateCreated

I've tried this, but doesn't work and i'm not sure how to do it. 

DECLARE @Time_In_Sweden datetime2, @UTCTimeNow datetime2
SET @Time_In_Sweden = DateCreated   -- ????????
SET @UTCTimeNow = GETUTCDATE()

UPDATE MachineStops SET DateCreated = CONVERT(DATEDIFF(d,DateCreated,@UTCTimeNow)

MachineStops is the table

Thanks.

Upvotes: 1

Views: 52

Answers (1)

Caldazar
Caldazar

Reputation: 3802

Well, since you know that time difference is 2 hours, easiest way is to just take those 2 hours off:

Update MachineStops SET DateCreated = DateADD(hour, -2, DateCreated )

Upvotes: 1

Related Questions