SQL Server DateTime vs Datetime2

by

In this SQL Server tutorial, we will learn about Datetime in SQL Server, Datetime2 in SQL Server, and will cover the following topics.

  • SQL Server Datetime vs Datetime2
  • SQL Server DateTime vs Datetime2(7)
  • SQL Server Datetime vs Datetime2(0)
  • SQL Server Datetime vs Datetime2 size
  • SQL Server Datetime vs Datetime2 precision
  • SQL Server DateTime vs Datetime2 performance
  • SQL Server DateTime vs Datetime2 vs Timestamp
  • SQL Server Datetime vs Datetime2 vs Smalldatetime
  • SQL Server DateTime vs Datetime2 vs Datetimeoffset

SQL Server Datetime vs Datetime2

Both DateTime and Datetime2 in SQL Server are data types mainly used to define data as well as time details. The datetime2 is an expansion of the existing DateTime type with a longer date range, higher default fractional precision, and a new feature of user-specified precision.

The DateTime is the most widely used data type for storing Date and time details together. The DateTime2 data type was introduced in SQL 2008 by Microsoft. Microsoft also suggests using Datetime2 instead of DateTime. So in this section, we will compare and contrast them.

The table below summarises some of the key differences between DateTime2 and DateTime.

FeatureDatetimeDatetime2
Syntaxdatetimedatetime2(n)
Where n represents fractional seconds precision
FormatYYYY-MM-DD hh-mm-ss.nnnYYYY-MM-DD hh-mm-ss.nnnnnnn
Date Range1753-01-01 To 9999-12-310001-01-01 To 9999-12-31
Time Range00:00:00 To 23:59:59.99700:00:00 To 23:59:59.9999999
Default Value1900-01-01 00:00:001900-01-01 00:00:00
AccuracyRounded to increments of .000, .003, or .007 seconds.0000001 seconds (100 nanoseconds )
ANSI SQL CompliantNoSQL Standards and is ISO Compliant (ISO 8601)
Time zone offsetNoNo
Character Length19 positions minimum &
23 maximum		19 positions minimum &
27 maximum
Storage Size8 bytes6 to 8 bytes, depending on the precision*
User-Defined PrecisionNoYes
UsageDECLARE @DateTime datetime

CREATE TABLE table_name ( Column1 datetime )		DECLARE @DateTime2 datetime2(7)

CREATE TABLE table_name ( Column1 datetime2(7) )

Read: Create Foreign Key in SQL Server

SQL Server Datetime vs Datetime2 Size

Till now, it is pretty clear that Datetime2 has additional capability than Datetime data type, which is the reason why Microsoft is recommending Datatime2 over DateTime. But do these additional capabilities require additional storage size?

The answer to this question is No.

Datetime2 uses 6 to 8 bytes of storage, whereas Datetime uses 8 bytes. The amount of space used by Datetime2 is determined by the fractional precision that we select for the column:

  • It will take 6 bytes for precision less than 3.
  • It will take 7 bytes for precision 3 or 4.
  • All other precision till 7 will requires 8 bytes.

The Datetime2 with fractional seconds precision of 3 will give the same result as given by Datetime data type. In addition, Datetime2(3) requires 7 bytes of storage rather than the 8 bytes used by the original DateTime datatype.

For demonstration, consider the following example given below.

--declaring current datatime and datetime2 values
DECLARE @DateTime DATETIME = GETDATE(),
        @DateTime2 DATETIME2(3)= SYSDATETIME()

--datatime and datetime2 values
SELECT @DateTime AS 'Datetime Value',
       @DateTime2 AS 'Datetime2(3) Value'
 
--datetime and datetime2 storage size 
SELECT DATALENGTH(@DateTime) AS 'Datetime Size',
       DATALENGTH(@DateTime2) AS 'Datetime2(3) Size'

In the example, first, we have declared two variables of Datetime and datetime2 data type respectively. After this, we are using the SELECT statement to view the values of Datetime and datetime2.

In the end, we are using the DATALENGTH function to get the storage size of Datetime and datetime2 data types.

And after successful execution, we will get the following result.

SQL Server Datetime vs Datetime2 Size
SQL Server Datetime vs Datetime2 Size

Read: Types of Backup in SQL Server

SQL Server Datetime vs Datetime2 Precision

The Datetime2 data type in SQL Server has a precision of 1⁄10000000 of a second, which means we can store 0.0000001 seconds as the smallest unit of time. Whereas, Datetime has a 1/300 second precision, and .003 second is the smallest unit of time that can be stored.

By using Datetime2 in SQL Server, we can even choose the fractional seconds precision whereas, the Datetime data type has fixed precision.

The maximum limit of fractional seconds precision in case Datetime is 3, whereas it is 7 in the case of Datetime2. The DateTime2(3) is a near approximation of the DateTime.

For better understanding, let’s consider the following example demonstrated below.

--declaring current datatime and datetime2 values
DECLARE @DateTime DATETIME = GETDATE(),
        @DateTime2 DATETIME2(5) = SYSDATETIME()

--datatime and datetime2 values
SELECT @DateTime AS 'Datetime Value',
       @DateTime2 AS 'Datetime2(5) Value'

In the above example, we are declaring the Datetime and Datetime2 variables using GETDATE() and SYSDATETIME() functions respectively. And for Datatime2, we have defined the precision as 5, which means the Datetime2 variable will have 5 digits for nanoseconds. After this, we are using the SELECT statement to show the variable values.

In the end, we will get the following output.

SQL Server Datetime vs Datetime2 precision
Final Output

Read: Saving changes is not permitted in SQL Server

SQL Server DateTime vs Datetime2(7)

So till now, we have discussed the difference between Datetime and Datetime2 in SQL Server and how Datetime2 is better than Datetime data type.

Now let’s understand how we can use user-defined precision in datetime2 and how it is different from standard DateTime.

In Datetime2, the maximum fractional seconds precision that we can define is 7, which means there can be 7 digits representing the nanosecond value. But we cannot define any fractional seconds precision value in the case of Datetime data type. The default value of fractional seconds precision is 3 for Datetime.

And for demonstration, consider the following example given below.

--declaring datatime and datetime2 variables
DECLARE @DateTime DATETIME = GETDATE(),
        @DateTime2 DATETIME2(7)= SYSDATETIME()

--datatime and datetime2 values
SELECT @DateTime AS 'Datetime Value',
       @DateTime2 AS 'Datetime2(7) Value'

In the example, we are using the GETDATE() function to define the current system DateTime value. And we are also using the SYSDATETIME() function to define the system date-time value in datetime2 format. And for Datetime2, we have defined the precision as 7.

After successfully executing the above query, we will get the following output.

SQL Server DateTime vs Datetime2
DateTime vs Datetime2(7)

Read SQL Server bulk insert from CSV file

SQL Server DateTime vs Datetime2(0)

So in the above section, we have learned how Datetime2 data type with precision 7 is different from standard Datetime. Now in this section, we will learn how the Datetime2 with precision 0 is different from the DateTime data type.

If we define fractional second precision as 0 in Datetime2, it simply means that there should be no digits for nanoseconds in the date-time value. So the Datetime2(0) data type will not have a nanoseconds value in it.

And for example, consider the following SQL query given below.

--declaring current datatime and datetime2 values
DECLARE @DateTime DATETIME = GETDATE(),
        @DateTime2 DATETIME2(0)= SYSDATETIME()

--datatime and datetime2 values
SELECT @DateTime AS 'Datetime Value',
       @DateTime2 AS 'Datetime2(0) Value'

After successfully executing the above code, we will get the following result.

SQL Server DateTime vs Datetime2 with precision 0
DateTime vs Datetime2(0)

Read: Identity Column in SQL Server

SQL Server DateTime vs Datetime2 Performance

Now in this section, we will discuss some of the important differences between Datetime and Datetime2 data types based upon performance factors.

  1. The date range for Datetime data type is from “1753-01-01” to “9999-12-31“, whereas for Datetime2 it is from “0001-01-01” to “9999-12-31“.
  2. The time range for Datetime data type is from “00:00:00” to “23:59:59.997“, whereas for Datetime2 it is from “00:00:00” to “23:59:59.9999999“.
  3. The character length for Datetime data type is from 19 positions minimum to 23 maximum. Whereas, the Datetime2 can have minimum 19 positions to maximum 27.
  4. The storage size for Datetime is fixed to 8 bytes and Datetime2 it varies from minimum 6 bytes to 8 bytes maximum. And for some cases the Datetime2 returns same result as Datetime, while using less storage.
  5. For Datetime2, we can define the fractional seconds precision from 0 to 7. But the Datatime data type do not accepts user-defined precision.
  6. The accuracy for Datetime data type is 0.00333 second, whereas the accuracy for Datatime2 is 100 nanoseconds.
  7. The default fractional precision for Datatime is 3 and for Datatime2 it is 7.

Read: Delete Duplicate Rows in SQL Server

SQL Server DateTime vs Datetime2 vs Datetimeoffset

In this post, we have already discussed DateTime and Datetime2, so now let’s discuss what is Datetimeoffset.

The datetimeoffset is a data type in SQL Server, that stores the date-time value, as well as an offset indicating how far that date-time departs from UTC. The syntax for Datetimeoffset is as follows.

DATETIMEOFFSET(fractional seconds precision)

All these data types are very much similar to each other. But there is some difference like datetimeoffset stores the time zone offset, whereas Datetime and Datetime2 only store date and time values.

The key differences between these categories are summarised in the table below.

FeatureDateTimeDatetime2Datetimeoffset
ANSI SQL CompliantNoYesYes
Date Range1753-01-01 To 9999-12-310001-01-01 To 9999-12-310001-01-01 To 9999-12-31
Time Range00:00:00 To 23:59:59.99700:00:00 To 23:59:59.999999900:00:00 To 23:59:59.9999999
Character Length19 positions minimum
23 maximum		19 positions minimum
27 maximum		26 positions minimum
34 maximum
Storage Size8 bytes6 to 8 bytes, depending on the precision*8 to 10 bytes, depending on the precision*
AccuracyRounded to increments of .000, .003, or .007 seconds100 nanoseconds100 nanoseconds
Fractional second precisionYesYesYes
User-Defined PrecisionNoYesYes
Time zone offset rangeNoneNone-14:00 through +14:00
Time zone offset awareness and preservationNoNoYes
UsageDECLARE @DateTime datetime

CREATE TABLE table_name ( Column1 datetime )		DECLARE @DateTime2 datetime2(7)

CREATE TABLE table_name ( Column1 datetime2(7) )		DECLARE @DateTimeOffset datetimeoffset(7)

CREATE TABLE table_name ( Column1 datetimeoffset(7) )

Now for demonstration, consider the following example given below.

--declaring current datatime, datetime2 and datetimeoffset variables
DECLARE @DateTime DATETIME = GETDATE(),
        @DateTime2 DATETIME2(7) = SYSDATETIME(),
	@DateTimeOffset DATETIMEOFFSET(7) = SYSDATETIMEOFFSET()

--datatime, datetime2 and datetimeoffset values
SELECT @DateTime AS 'Datetime Value',
       @DateTime2 AS 'Datetime2(7) Value',
       @DateTimeOffset AS 'Datetimeoffset(7) Value'

In the above example, we are using some functions to declare DateTime, datetime2, and datetimeoffset variables. After this, we are using the SELECT statement to display all the values. And after executing the example, we will get the following result.

SQL Server DateTime vs Datetime2 vs Datetimeoffset
DateTime vs Datetime2 vs Datetimeoffset

Read: SQL Server Create Temp Table

SQL Server Datetime vs Datetime2 vs Smalldatetime

The smalldatetime in SQL Server is a data type used to store the date and time values without any fractional second precision. And also, the seconds part in time is always zero while using smalldatetime data type.

The key differences between these categories are outlined in the table below.

FeatureDateTimeDatetime2Smalldatetime
ANSI SQL CompliantNoYesNo
Date Range1753-01-01 To 9999-12-310001-01-01 To 9999-12-311900-01-01 To 2079-06-06
Time Range00:00:00 To 23:59:59.99700:00:00 To 23:59:59.999999900:00:00 To 23:59:59
Character Length19 positions minimum
23 maximum		19 positions minimum
27 maximum		19 positions maximum
Storage Size8 bytes6 to 8 bytes, depending on the precision*4 bytes
AccuracyRounded to increments of .000, .003, or .007 seconds100 nanoseconds1 minute
Fractional second precisionYesYesNo
User-Defined PrecisionNoYesNo
UsageDECLARE @DateTime datetime

CREATE TABLE table_name ( Column1 datetime )		DECLARE @DateTime2 datetime2(7)

CREATE TABLE table_name ( Column1 datetime2(7) )		DECLARE @SmallDateTime smalldatetime

CREATE TABLE table_name ( Column1 smalldatetime )

Now for demonstration, consider the following example given below.

--declaring current datatime, datetime2 and smalldatetime variables
DECLARE @DateTime DATETIME = GETDATE(),
        @DateTime2 DATETIME2(7) = SYSDATETIME(),
	@SmallDateTime SMALLDATETIME = GETDATE()

--datatime, datetime2 and smalldatetime values
SELECT @DateTime AS 'Datetime Value',
       @DateTime2 AS 'Datetime2(7) Value',
       @SmallDateTime AS 'Smalldatetime Value'

In the above example, we are using some functions to declare DateTime, datetime2, and smalldatetime variables. After this, we are using the SELECT statement to display all the values. And after executing the example, we will get the following output.

SQL Server DateTime vs Datetime2 vs Smalldatetime
DateTime vs Datetime2 vs Smalldatetime

Read: SQL Server Row_Number – Complete tutorial

SQL Server DateTime vs Datetime2 vs Timestamp

The timestamp data type is a synonym for the rowversion data type in SQL Server. And Microsoft also suggests using rowversion instead of the timestamp in DDL statements.

In comparison with Datetime and datetime2, the timestamp has nothing to do with the date and time values. Also, the timestamp data type is no longer supported in SQL Server.

So, let’s understand the rowversion data type instead of timestamp.

Within a database, rowversion is a data type that exposes automatically generated, unique binary numbers. It allows us to use a unique value to version-stamp table rows. When numerous users are updating records at the same time, this helps keep the database safe.

Now for demonstration, consider the following example given below.

--Creating Table with rowversion data type
create table SampleTable (
	id INT,
	Name VARCHAR(50),
	RowVer ROWVERSION
);
insert into SampleTable (id, Name) values (1, 'Cassie Kincaid');
insert into SampleTable (id, Name) values (2, 'Jose Shaul');
insert into SampleTable (id, Name) values (3, 'Pepillo Widocks');
insert into SampleTable (id, Name) values (4, 'Woodie MacRannell');
insert into SampleTable (id, Name) values (5, 'Jesselyn Guyton');

--Querying the created table
SELECT * FROM SampleTable

In the above example, we are creating a sample table with 3 columns, out of which one is of rowversion data type. After this, we are inserting some records in the table, but we have not specified any value for rowversion column. For the rowversion column, SQL Server will automatically generate unique values.

And if we query the table, we will get the following result.

rowversion in sql server
Output

You may also like the following SQL server tutorial:

So in this SQL Server tutorial, we have learned about Datetime in SQL Server, Datetime2 in SQL Server, and we have also covered the below-given topics.

  • SQL Server Datetime vs Datetime2
  • SQL Server DateTime vs Datetime2(7)
  • SQL Server Datetime vs Datetime2(0)
  • SQL Server Datetime vs Datetime2 size
  • SQL Server Datetime vs Datetime2 precision
  • SQL Server DateTime vs Datetime2 performance
  • SQL Server DateTime vs Datetime2 vs Timestamp
  • SQL Server Datetime vs Datetime2 vs Smalldatetime
  • SQL Server DateTime vs Datetime2 vs Datetimeoffset