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:

1. 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?

2. 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

Answer 1

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