DateTime vs DateTime2 in Sql Server

In SQL Server, DateTime and DateTime2 are data types used to define a date combined with a time of day in a 24-hour clock format.

Microsoft recommends using DateTime2 instead of DateTime as it is more portable and provides more seconds precision. Also, DateTime2 has a larger date range and optional user-defined seconds precision with higher accuracy. Datetime2 aligns with SQL standards.

The key differences between DateTime and DateTime2 are listed in the table below

The following example demonstrates the precision difference between DateTime and DateTime2.

DECLARE @datetime2 datetime2(7), 
  @datetime datetime;

SET @datetime2 = GETDATE();
SET @datetime = GETDATE();

SELECT @datetime AS 'datetime', @datetime2 AS 'datetime2';

In the above example, the precision of both DateTime and DateTime2 is displayed for the current date GETDATE (). Datetime2 has fractional seconds precision of 7 which means that there are 7 digits representing the nanosecond value whereas DateTime has a precision of 3.

The following example demonstrates the fractional seconds rounding off.

DECLARE @datetime datetime, @datetime2 datetime2(3);
    
SET @datetime2 = '2022-11-23 10:45:30.6782222';
SET @datetime = @datetime2;

SELECT @datetime AS 'datetime', 
    @datetime2 AS 'datetime2';

Here, the datetime2 fractional seconds precision is set to 3 which is the same as that of datetime. Datetime2 is assigned a date string literal and DateTime is assigned DateTime2 variable value. In the result, the value for DateTime is rounded off to 677 and for DateTime2 it remains at 678

Note: You will get a conversion error if you directly assign a string literal with more than 3 fractional seconds precision for DateTime variable i.e., you get an error if you directly assign '2022-11-23 10:45:30.6782222' value to DateTime variable (@datetime) as its precision is 7.

Storage space of DateTime and DateTime2

The following example demonstrates the memory space occupies by DateTime and DateTime2 data types.

DECLARE @datetime datetime,
  @datetime2 datetime2(3);

SET @datetime2 = GETDATE();
SET @datetime = GETDATE();

SELECT DATALENGTH(@datetime) AS 'datetime',
	  DATALENGTH(@datetime2) AS 'datetime2';

Both DateTime and DateTime2 are passed with the current date GETDATE(). The DATALENGTH of both variables gives the size occupied by each data type. As you can see in the above result, DateTime occupies 8 bytes whereas DateTime2 with fractional seconds precision of 3, occupies 7 bytes.

Simple Math Operation on DateTime and DateTime2

It is important to note that there are a few drawbacks of using DateTime2. You cannot do basic math operations with dates of DateTime2 data type, like adding a number to a date. Whereas it works with a DateTime data type.

Below is an example to add a day to the current date for a DateTime and DateTime2 data types.

Add a day to DateTime data type. This works as expected and the result displays the next day.

DECLARE @datetime datetime;

SET @datetime = GETDATE();

SELECT @datetime + 1 as Tomorrow;

Add a day to DateTime2 data type. This doesn't work like expected and you will get an error as shown in the result.

DECLARE @datetime2 datetime2(7);
 
SET @datetime2 = GETDATE();

SELECT @datetime2 + 1 as Tomorrow;

TutorialsTeacher

Author

We are a team of passionate developers, educators, and technology enthusiasts who, with their combined expertise and experience, create in-depth, comprehensive, and easy to understand tutorials. We focus on a blend of theoretical explanations and practical examples to encourages hands-on learning. Learn more about us