Changing the default constraints for all the date time columns from Local time to UTC time standards.

Problem:

Using applications from different time zones, when we store the default values of the columns as getdate(), it becomes a nightmare to convert all those dates to one standard time zone.

Solution:

GETUTCDATE

Returns a datetime value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).

Example:

In this example I am going to show how we find out all the datetime columns with default constraint of getdate() and altering those default constraints to getutcdate().

Sample Script:

DECLARE @SqlConstraint NVARCHAR(max) = ''

                ,@CurrentColumnName VARCHAR(100)

                ,@CurrentConstraintName NVARCHAR(200)

                ,@CurrentSchemaName VARCHAR(100)

                ,@CurrentTableName VARCHAR(100)

                ,@NL VARCHAR(2) = CHAR(13) + CHAR(10)

-- Cursor for looping through the table and generate scripts dynamically to execute drop and  create constraint statements.

DECLARE @TableCursor AS CURSOR

SET @TableCursor = CURSOR

FOR

SELECT sc.[Name] AS SchemaName

                ,t.[NAME] AS TableName

                ,c.[NAME] AS ColumnName

                ,dc.[NAME] AS ConstraintName

FROM sys.all_columns c

INNER JOIN sys.tables t ON c.object_id = t.object_id

INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id

INNER JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id

WHERE sc.NAME = 'dbo'

                AND dc.[definition] = '(getdate())'

OPEN @TableCursor

FETCH NEXT

FROM @TableCursor

INTO @CurrentSchemaName

                ,@CurrentTableName

                ,@CurrentColumnName

                ,@CurrentConstraintName

WHILE @@FETCH_STATUS = 0

BEGIN

                -- SQL Statement for Constriant

                SET @SqlConstraint = ' ALTER TABLE [' + @CurrentSchemaName + '].[' + @CurrentTableName + ']'

                SET @SqlConstraint = @SqlConstraint + ' DROP CONSTRAINT  [' + @CurrentConstraintName + ']' + @NL

                SET @SqlConstraint = @SqlConstraint + ' ALTER TABLE [' + @CurrentSchemaName + '].[' + @CurrentTableName + ']'

                SET @SqlConstraint = @SqlConstraint + ' ADD CONSTRAINT  [' + @CurrentConstraintName + ']  DEFAULT (getutcdate()) FOR [' + @CurrentColumnName + ']' + @NL

                -- Alter default constraint  from getdate() to getutcdate()

                EXECUTE sp_executesql @SqlConstraint

                PRINT 'Dropped and Created Constraint ' + @CurrentConstraintName + ' ON ' + @CurrentTableName

                FETCH NEXT

                FROM @TableCursor

                INTO @CurrentSchemaName

                                ,@CurrentTableName

                                ,@CurrentColumnName

                                ,@CurrentConstraintName

END

CLOSE @TableCursor

DEALLOCATE @TableCursor

GO