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?

marc_s · Accepted Answer · 2014-04-18 11:41:45Z

13

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)

edited Apr 18, 2014 at 11:41

answered Apr 18, 2014 at 11:36

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

keenUser Over a year ago

Perfect. I used SYSUTCDATETIME() as default with DATETIME2. While inserting the values in the table, I take care of inserting them in UTC format only.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related