How to create a date time offset column that defaults to the current utc date time

Question

I want to create a table with a CreatedOn column, of type DateTimeOffset, and when a record is inserted, I want the current UTC date and time to be used.

If I add a default constraint of GETUTCDATE(), it puts in the local time with a 0 offset. If I use SYSUTCDATETIME() it puts in the local time with the correct offset.

Is it possible to get the utc time in with a 0 offset?

Answer 1

I suppose you could just force it to what ever offset you want depending on what the SYSUTCDATETIME() or any other date time stamp is. You can use the below and change the offset to what ever you want it to be and use GETDATE() instead of GETUTCDATE(), etc...

SELECT GETUTCDATE() AS UTC_DT, SYSUTCDATETIME() AS SYS_UTC_DT, SYSDATETIMEOFFSET() AS SYS_DT_OFFSET

SELECT CAST(TODATETIMEOFFSET(GETUTCDATE(), '+00:00') AS DATETIME2) AS UTC_WITH_OFFSET   --SQL SERVER 2008 and onward...
SELECT GETDATE() AT TIME ZONE 'UTC'                                                     --SQL SERVER 2016 only...