DATETIME or DATETIME2(3)? - Migration to Microsoft SQL Server

Starting from SQL Server 2008, you can use DATETIME2(p) data type with fractional seconds precision up to 7 digits (0.1 microseconds) in Microsoft SQL Server.

Although it is clear that you can use DATETIME2 to store more fractional seconds, is there any benefit of using DATETIME2(3) i.e with milliseconds precision that is also provided by the traditional DATETIME data type?

Summary:

	DATETIME2(3)	DATETIME
Precision	Exact milliseconds	Rounding to .xx0, .xx3 and .xx7
Default casting to string	yyyy-mm-dd hh:mi:ss.mmm	mon dd yyyy hh:miAM

Although DATETIME data type can store milliseconds, it cannot store any millisecond value, the value is always rounded to the nearest .xx0, .xx3 or .xx7 value.

SQL Server:

  -- Note .015 to .017 conversion
  SELECT CAST('2017-12-29 11:22:38.015' AS DATETIME)
  # 2017-12-29 11:22:38.017
 
  -- Note .719 to .720 conversion
  SELECT CAST('2017-12-29 11:22:38.719' AS DATETIME)
  # 2017-12-29 11:22:38.720
 
  -- Note .012 to .013 conversion
  SELECT CAST('2017-12-29 11:22:38.012' AS DATETIME)
  # 2017-12-29 11:22:38.013

At the same time DATETIME2(3) data type allows you to store the exact values:

SQL Server:

  SELECT CAST('2017-12-29 11:22:38.015' AS DATETIME2(3))
  # 2017-12-29 11:22:38.015
 
  SELECT CAST('2017-12-29 11:22:38.719' AS DATETIME2(3))
  # 2017-12-29 11:22:38.719
 
  SELECT CAST('2017-12-29 11:22:38.012' AS DATETIME2(3))
  # 2017-12-29 11:22:38.012

In most cases DATETIME2(3) is more preferred data type for migration from other databases to SQL Server since you can store the exact millisecond values and do not introduce discrepancies when migrating datetime values.

Another difference between DATETIME and DATETIME2 data types is the default casting to string:

SQL Server:

  DECLARE @d DATETIME2(3) = GETDATE()
  DECLARE @d1 DATETIME = GETDATE()
 
  -- Default format is yyyy-mm-dd hh:mi:ss.mmm for DATETIME2
  SELECT CAST(@d AS VARCHAR)
  # 2017-12-29 12:14:26.377
 
  -- Default format is mon dd yyyy hh:miAM for DATETIME
  SELECT CAST(@d1 AS VARCHAR)
  # Dec 29 2017 12:14PM

Format yyyy-mm-dd hh:mi:ss.mmm is more widely used, it is guided by ISO/ODBC and many applications may expect it rather than mon dd yyyy hh:miAM.

Of course, you can explicitly define the format when converting DATETIME values to string, but this requires additional changes in applications:

SQL Server:

  -- Converting DATETIME to yyyy-mm-dd hh:mi:ss.mmm
  SELECT CONVERT(DATETIME, GETDATE(), 121)
  # 2017-12-29 12:14:26.377

During migration you may want to utilize the maximum DATETIME2 precision and convert datetime data types to DATETIME2(7) rather than DATETIME2(3).

Sometimes it can cause problems, for example, if there is a user-defined function or application logic that expects milliseconds at the specified position after conversion to string:

  DECLARE @d DATETIME2(3) = GETDATE()
 
  -- Expression extracts last 3 digits expecting milliseconds
  SELECT CAST(RIGHT(CAST(@d AS VARCHAR), 3) AS INTEGER) AS ms

In the example above, the expression converts datetime value to string and extracts last digits (millisecons). Note that this code will not work if you declare @d as DATETIME or DATETIME2(7).

For DATETIME the conversion to string will produce 4AM as last 3 characters, for DATETIME2(7) last 3 characters will be 100-0.1 microseconds.

For more information, see Migration Reference.