How to update all datetime records to UTC time in a SQL Server database?

Question

I have a SQL Server database with ~1000 tables. Most of the tables have datetime columns with different name. Currently the value stored there is with offset because the code inserted it with the current date from the server.

The goal is to have all datetime records in UTC time.

Now I want to do two things:

Using some of these 3 functions (SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME) I can get the offset. Next step is to find all datetime records and update them. Can you help me with this?
Some of the datetime columns have default value GETTIME. I want to update that with SYSUTCDATETIME. Any idea how?

PS: I do not want to change the columns' type to datetimeoffset because the type is not supported in MSSQL Server 2005.

EDIT: Because there are so many comments about datetime and the topic of the question was shifted in different direction I suggest you not to think about datetime column type but for int. The problem is still the same. Updating many tables and changing the default value of columns.

Best Regards mynkow

You can't do it that way. The difference between local and utc is not constant (daylight savings and more). — usr, Sep 14 '12 at 20:08
@usr: why not, if updated time is between April-November(summer time) I will add +1. — mynkow, Sep 14 '12 at 20:19
@ClearLogic: The answer is in the first line of the question: Most of the tables have datetime. The offeset is included in the datetime, not a separate value => http://msdn.microsoft.com/en-us/library/ms186724.aspx — mynkow, Sep 14 '12 at 20:21
how on earth you can store timezone offset in Datetime. even MSDN says NO in the link you sent http://stackoverflow.com/questions/1826837/how-to-store-timezone-in-sql-server-2005 — ClearLogic, Sep 14 '12 at 23:08
How do you get the offset in TSQ? This fails for me select DATEPART(TZoffset, GETDATE()) — paparazzo, Sep 14 '12 at 23:11
to get server’s current time zone offset SELECT DATENAME (TZoffset, SYSDATETIMEOFFSET()) — ClearLogic, Sep 14 '12 at 23:17
@ClearLogic thanks I do email threading were I need to match a subject with a date and think this will fix a long standing problem. I did not know about type datetimeoffset. — paparazzo, Sep 15 '12 at 00:10
@ClearLogic: Let's say that the server (application+DB) is in time zone +1 and current UTC is 1am. The datetime value in the database will be 2am. This is what I meant with 'offset included'. In my prev comment I said that offset is not a separate value. — mynkow, Sep 15 '12 at 18:02

John Dewey · Accepted Answer · 2012-09-16T21:11:17.130

Dynamic SQL to update all user databases on your server. The first query outputs to the Results tab. The second query outputs to the Messages tab.

SET NOCOUNT ON;
GO

---------------------------------------------
-- #1 Dynamic SQL to update DATETIME values with UTC offset
DECLARE @t TABLE(TABLE_CATALOG VARCHAR(128), TABLE_SCHEMA VARCHAR(128), TABLE_NAME VARCHAR(128), COLUMN_NAME VARCHAR(128));

INSERT @t
EXEC sp_msforeachdb 'select db = "?"
, s.name
, t.name
, c.name
FROM [?].sys.tables t
JOIN [?].sys.columns c ON c.object_id = t.object_id
JOIN [?].sys.types y ON y.user_type_id = c.user_type_id
JOIN [?].sys.schemas s ON s.schema_id = t.schema_id
WHERE t.[type] = ''U''
AND y.name = ''DATETIME''
AND "?" NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')';

DECLARE @Offset INT;
SET @Offset = - DATEPART(TZOFFSET, SYSDATETIMEOFFSET());

SELECT [SQL] = 'UPDATE ['+ C.TABLE_CATALOG +'].[' + C.TABLE_SCHEMA + '].[' + C.TABLE_NAME + '] SET [' + C.COLUMN_NAME + '] = DATEADD(MINUTE,' + CAST(@Offset AS VARCHAR(5)) + ',[' + C.COLUMN_NAME + ']);' 
FROM @t C;
GO

---------------------------------------------
-- #2 Dynamic SQL to change DATETIME column defaults to SYSUTCDATETIME
DECLARE @t TABLE([SQL] VARCHAR(MAX));
DECLARE @SQL VARCHAR(MAX);

INSERT @t   
EXEC sp_msforeachdb 'SELECT [SQL] = ''---------------------------------------------'' + CHAR(13) + CHAR(10)
    + ''-- [?].[''+s.name+''].[''+t.name+''].['' + c.name + '']'' + CHAR(13) + CHAR(10)
    + ''ALTER TABLE [?].[''+s.name+''].[''+t.name+'']'' + CHAR(13) + CHAR(10)
    + ''DROP CONSTRAINT [''+d.name + '']'' + CHAR(13) + CHAR(10)
    + ''GO'' + CHAR(13) + CHAR(10)
    + ''ALTER TABLE [?].[''+s.name+''].[''+t.name+''] ADD CONSTRAINT'' + CHAR(13) + CHAR(10)
    + ''[''+d.name+''] DEFAULT (SYSUTCDATETIME()) FOR [''+c.name + '']'' + CHAR(13) + CHAR(10)
    + ''GO'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    FROM [?].sys.default_constraints d
    JOIN [?].sys.columns c  ON  c.default_object_id = d.object_id
    JOIN [?].sys.types y ON y.user_type_id = c.user_type_id
    JOIN [?].sys.tables t ON t.object_id = d.parent_object_id AND t.[type] = ''U''
    JOIN [?].sys.schemas s ON s.schema_id = t.schema_id
    WHERE y.name = ''datetime''
    AND "?" NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')';

DECLARE C CURSOR FOR 
SELECT * FROM @t

OPEN C
FETCH NEXT FROM C INTO @SQL;
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @SQL;
    FETCH NEXT FROM C INTO @SQL;
END

CLOSE C;
DEALLOCATE C;
GO

How can I have the SQL statements executed as well rather than just being printed out? — Hampton Terry, Nov 03 '14 at 18:49

How to update all datetime records to UTC time in a SQL Server database?

1 Answers1