Since our business in getting more international, I needed to convert our database which has many default constraints of "GETDATE()" to "GETDATEUTC()". I wrote a little SQL script to locate all default constraints containing "GETDATE()" and replace them with "GETUTCDATE()". It also converts all the old date values to UTC time.
set nocount on
declare @tableName varchar(255)
declare @columnName varchar(255)
declare @constraintName varchar(255)
declare @newConstraintName varchar(255)
declare @myDropCmd varchar(512)
declare @myCreateCmd varchar(512)
declare @timeZoneCommand varchar(512)
declare @timeZoneOffset int
select @timeZoneOffset = Datediff(hh,GETDATE(),GETUTCDATE())
-- original query from MeanOldDBA at
-- http://www.developmentnow.com/g/113_2004_9_0_0_433030/Search-for-defaults-in-table-DDL.htm
DECLARE mycursor CURSOR FOR
SELECT
so1.name AS table_name,
sc1.name AS column_name,
so2.name AS default_name
FROM
sysobjects so1
INNER JOIN syscolumns sc1 ON so1.id = sc1.id
INNER JOIN sysobjects so2 ON sc1.cdefault = so2.id
INNER JOIN syscomments sc2 ON so2.id = sc2.id
WHERE
sc2.text LIKE '%getdate()%'
OPEN mycursor
FETCH NEXT FROM mycursor INTO @tableName, @columnName, @constraintName
WHILE (@@fetch_status = 0) -- while OK
BEGIN
print '-- Table: ' + @tableName + ', Constraint: ' + @constraintName+ ', Column: ' + @columnName
SELECT @myDropCmd = ' ALTER TABLE ['+@tableName+'] DROP CONSTRAINT ['+@constraintName+']'
PRINT @myDropCmd
EXECUTE (@myDropCmd)
SELECT @newConstraintName = 'DF_'+@tableName+'_dtime_utc'
SELECT @myCreateCmd = ' ALTER TABLE ['+@tableName+'] ADD CONSTRAINT ['+@newConstraintName+'] DEFAULT (GETUTCDATE()) FOR ['+@columnName+']'
PRINT @myCreateCmd
EXECUTE (@myCreateCmd)
select @timeZoneCommand = ' UPDATE ' + @tableName + ' SET ' + @columnName + ' = DATEADD(HOUR, '+convert(varchar(3),@timeZoneOffset)+', '+@columnName+')'
PRINT @timeZoneCommand
EXECUTE (@timeZoneCommand)
FETCH NEXT FROM mycursor INTO @tableName, @columnName, @constraintName
END
CLOSE mycursor
DEALLOCATE mycursor
GO