Create a datetime column in SQL Server 2008 with default value as current timestamp UTC

Question

I am creating a table in SQL Server 2008 like this:

create table Dummy
(
   id int,
   status int,
   node_id varchar(512),
   createdDTTM datetime NOT NULL default CURRENT_TIMESTAMP
);

However I want to specify a UTC timezone as default. I know in postgreSQL we have something like createdDTTM TIMESTAMP NOT NULL DEFAULT (now() at time zone 'utc')

Can we do similar thing here?

Answer 1

For times with timezone information, use DATETIMEOFFSET in SQL Server (2008 and newer):

create table dbo.Dummy
(
   id int,
   status int,
   node_id varchar(512),
   createdDTTM DateTimeOffset NOT NULL default SYSDATETIMEOFFSET()
);

Using the SYSDATETIMEOFFSET() you're getting the default current date and time as DATETIMEOFFSET (in the local timezone your server is located in) from SQL Server.

Or maybe you're looking for SYSUTCDATETIME() instead which gives you the current date and time in UTC format? This works just fine with DATETIME2(n) or DATETIMEOFFSET columns (in SQL Server 2008 and newer, I'd recommend not to use DATETIME anymore)