how to set default constraint to UTC time with TimeZone in SQL Server

Question

I have to add default value to a datetime column with UTC time with Timezone, right now I am using the GETUTCDATE() in default constraint, but it is not adding the timezone information to the column.

Please help me.

Answer 1

The type that includes timezone information (specifically, the offset) is datetimeoffset. GETUTCDATE returns a plain old DATETIME which has no timezone indication, not even a Local vs UTC indicator.

If you care about timezones and offsets, use a datetimeoffset column and ` SYSDATETIMEOFFSET as its default

Answer 2

You need the DATETIMEOFFSET data type and the SWITCHOFFSET function

DECLARE @t TABLE 
(
 id INT,
 dt DATETIMEOFFSET DEFAULT (SWITCHOFFSET(SYSDATETIMEOFFSET(),'+00:00'))
);

INSERT @t(id) VALUES(1);

SELECT id, dt, SYSDATETIMEOFFSET() 
FROM @t;

Since 2016 you can use AT TIME ZONE https://learn.microsoft.com/en-ru/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017