Stuffing a datetime value into a datetime2 column

Today I saw the following statement in a code review:

ALTER TABLE dbo.MyTable ADD CreateDate DATETIME2(0) NOT NULL DEFAULT GETUTCDATE()

The GETUTCDATE() function returns a datetime and is then stuffed into a datetime2 column.

Does it have any significance that the two types are different? Would it be better to use SYSUTCDATETIME() to create a datetime2 value?

Your column CreateDate is using DATETIME2(0) for the date definition. Inserting either SYSUTCDATETIME() or GETUTCDATE() has no difference when retrieving the values from the table.

Let's take your table definition and turn it into an example:

CREATE TABLE dbo.MyTable 
  (
  ID int IDENTITY (1,1),
  CreateDate DATETIME2(0) NOT NULL DEFAULT GETUTCDATE(),
  CreateDateSYS DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
  );

We then add some values to the table:

INSERT INTO MyTable (CreateDate, CreateDateSYS) 
VALUES 
  (GETUTCDATE(),GETUTCDATE()),
  (SYSUTCDATETIME(), SYSUTCDATETIME()),
  ('2023-03-30 07:16:45.1','2023-03-30 07:16:45.1'),
  ('2023-03-30 07:16:45.12','2023-03-30 07:16:45.12'),
  ('2023-03-30 07:16:45.123','2023-03-30 07:16:45.123'),
  ('2023-03-30 07:16:45.1234','2023-03-30 07:16:45.1234'),
  ('2023-03-30 07:16:45.12345','2023-03-30 07:16:45.12345')
  ;

Let's see if there is a difference between SYSUTCDATETIME() and GETUTCDATE():

SELECT * FROM MyTable;

ID
CreateDate
CreateDateSYS

1
2023-03-30 07:21:12
2023-03-30 07:21:12

2
2023-03-30 07:21:12
2023-03-30 07:21:12

3
2023-03-30 07:16:45
2023-03-30 07:16:45

4
2023-03-30 07:16:45
2023-03-30 07:16:45

5
2023-03-30 07:16:45
2023-03-30 07:16:45

6
2023-03-30 07:16:45
2023-03-30 07:16:45

7
2023-03-30 07:16:45
2023-03-30 07:16:45

As you can see, inserting GETUTCDATE() or SYSUTCDATETIME() has no impact on what is stored. This is because the columns (as in your example) had been defined using DATETIME2(0) which is the default precision.

If we create a table that has columns using DATETIME2(7) (which is a higher precision) and insert different values, then we receive the following results.

First we create the table:

CREATE TABLE dbo.MyTableFixed 
  (
  ID int IDENTITY (1,1),
  CreateDate DATETIME2(7) NOT NULL DEFAULT GETUTCDATE(),
  CreateDateSYS DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME()
  );

Then we insert some datetime values with varying precision:

INSERT INTO MyTableFixed (CreateDate, CreateDateSYS) 
VALUES 
  (GETUTCDATE(),GETUTCDATE()),
  (SYSUTCDATETIME(), SYSUTCDATETIME()),
  ('2023-03-30 07:16:45.1','2023-03-30 07:16:45.1'),
  ('2023-03-30 07:16:45.12','2023-03-30 07:16:45.12'),
  ('2023-03-30 07:16:45.123','2023-03-30 07:16:45.123'),
  ('2023-03-30 07:16:45.1234','2023-03-30 07:16:45.1234'),
  ('2023-03-30 07:16:45.12345','2023-03-30 07:16:45.12345')
  ;

We then select the values from the table:

SELECT * FROM MyTableFixed;

Which returns the following results:

ID
CreateDate
CreateDateSYS

1
2023-03-30 07:21:11.5500000
2023-03-30 07:21:11.5500000

2
2023-03-30 07:21:11.5508485
2023-03-30 07:21:11.5508485

3
2023-03-30 07:16:45.1000000
2023-03-30 07:16:45.1000000

4
2023-03-30 07:16:45.1200000
2023-03-30 07:16:45.1200000

5
2023-03-30 07:16:45.1230000
2023-03-30 07:16:45.1230000

6
2023-03-30 07:16:45.1234000
2023-03-30 07:16:45.1234000

7
2023-03-30 07:16:45.1234500
2023-03-30 07:16:45.1234500

Note:

The examples used in this answer can be run and tested over on db<>fiddle

Answering Your Questions

Does it have any significance that the two types are different?

In your case not, because you defined your column CreateDate with a low precision DATETIME2(0) type.

Would it be better to use SYSUTCDATETIME() to create a datetime2 value?

In your case not, because even though you would be creating a DATETIME2 value with a high precision, you are storing it in a DATETIME2(0) column which has a lower precision.

If your question were to encompass a possible performance impact when inserting data...

...then there can be a slight overhead, when certain values are passed to the column. This is due to an implicit conversion that occurs. This can be obtained from the execution plan.

-
When inserting dates into the DATETIME2(0) defined column you may observe

CONVERT_IMPLICIT(datetime2(0),[@1],0)              -- actual value 
CONVERT_IMPLICIT(datetime2(0),getutcdate(),0)      -- getutcdate()
CONVERT_IMPLICIT(datetime2(0),sysutcdatetime(),0)  -- sysutcdatetime()

-
When inserting dates into the DATETIME2(7) defined column you may observe

CONVERT_IMPLICIT(datetime2(7),[@1],0)              -- actual value 
CONVERT_IMPLICIT(datetime2(7),getutcdate(),0)      -- getutcdate()

-
You will not observe an implicit conversion when inserting sysutcdatetime() into the DATETIME2(7) defined column. This is because the values does not have to be converted from one datetime format to another. SYSUTCDATETIME() will return a true DATETIME2(7) formatted value.

Scalar Operator(sysutcdatetime())                  -- sysutcdatetime()

Following a screen shot of an execution plan containing the CONVERT_IMPLICIT conversion:

Possible Solution

If you were to ALTER your column to use DATETIME2(7) which stores a higher precision DATETIME2 value, then you could benefit from switching to SYSUTCDATETIME().

If you don't require such a

StackExchange Database Administrators Q#325365, answer score: 6