One day, while working on a project. I hit a wall.

The wall says, the date and time should be displayed according to the local datetime settings.

E.g: A visitor while visiting the site, should display the US date and time (dd/mm/yyyy), where as a user visiting the site from india, should have india date and time (dd/mm/yyyy).

Upon researching further, I realized that the date and time should be stored in “Coordinated universal time” aka UTC (don’t ask me, why it is not CUT. I don’t know). I already have database table designed and created and in use.

Most of the tables have datetime column which stores the dateandtime when the record is created or updated.
But those columns were storing the value from the sql server function GetDate().

GetDate() gives you the local date and time. To support multi region date formats. I should store the datetime in UTC format which is returned from the sqlserver function GETUTCDATE()

Checks the results for yourself,

SELECT GETUTCDATE(), GETDATE()

So, I have to replace all the default constraints which has getdate() to getutcdate().

To get a list of default value constraints

SELECT
TableName = sc.Name + '.' + t.Name,
ColumnName = c.Name,
dc.Name,
dc.definition
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
inner join sys.schemas sc on t.schema_id = sc.schema_id