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.
Feature | Datetime | Datetime2 |
---|---|---|
Syntax | datetime | datetime2(n) Where n represents fractional seconds precision |
Format | YYYY-MM-DD hh-mm-ss.nnn | YYYY-MM-DD hh-mm-ss.nnnnnnn |
Date Range | 1753-01-01 To 9999-12-31 | 0001-01-01 To 9999-12-31 |
Time Range | 00:00:00 To 23:59:59.997 | 00:00:00 To 23:59:59.9999999 |
Default Value | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 |
Accuracy | Rounded to increments of .000, .003, or .007 seconds | .0000001 seconds (100 nanoseconds ) |
ANSI SQL Compliant | No | SQL Standards and is ISO Compliant (ISO 8601) |
Time zone offset | No | No |
Character Length | 19 positions minimum & 23 maximum | 19 positions minimum & 27 maximum |
Storage Size | 8 bytes | 6 to 8 bytes, depending on the precision* |
User-Defined Precision | No | Yes |
Usage | DECLARE @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.

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.

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.

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.

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.
- 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“.
- 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“.
- 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.
- 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.
- For Datetime2, we can define the fractional seconds precision from 0 to 7. But the Datatime data type do not accepts user-defined precision.
- The accuracy for Datetime data type is 0.00333 second, whereas the accuracy for Datatime2 is 100 nanoseconds.
- 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.
Feature | DateTime | Datetime2 | Datetimeoffset |
---|---|---|---|
ANSI SQL Compliant | No | Yes | Yes |
Date Range | 1753-01-01 To 9999-12-31 | 0001-01-01 To 9999-12-31 | 0001-01-01 To 9999-12-31 |
Time Range | 00:00:00 To 23:59:59.997 | 00:00:00 To 23:59:59.9999999 | 00:00:00 To 23:59:59.9999999 |
Character Length | 19 positions minimum 23 maximum | 19 positions minimum 27 maximum | 26 positions minimum 34 maximum |
Storage Size | 8 bytes | 6 to 8 bytes, depending on the precision* | 8 to 10 bytes, depending on the precision* |
Accuracy | Rounded to increments of .000, .003, or .007 seconds | 100 nanoseconds | 100 nanoseconds |
Fractional second precision | Yes | Yes | Yes |
User-Defined Precision | No | Yes | Yes |
Time zone offset range | None | None | -14:00 through +14:00 |
Time zone offset awareness and preservation | No | No | Yes |
Usage | DECLARE @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.

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.
Feature | DateTime | Datetime2 | Smalldatetime |
---|---|---|---|
ANSI SQL Compliant | No | Yes | No |
Date Range | 1753-01-01 To 9999-12-31 | 0001-01-01 To 9999-12-31 | 1900-01-01 To 2079-06-06 |
Time Range | 00:00:00 To 23:59:59.997 | 00:00:00 To 23:59:59.9999999 | 00:00:00 To 23:59:59 |
Character Length | 19 positions minimum 23 maximum | 19 positions minimum 27 maximum | 19 positions maximum |
Storage Size | 8 bytes | 6 to 8 bytes, depending on the precision* | 4 bytes |
Accuracy | Rounded to increments of .000, .003, or .007 seconds | 100 nanoseconds | 1 minute |
Fractional second precision | Yes | Yes | No |
User-Defined Precision | No | Yes | No |
Usage | DECLARE @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.

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.

You may also like the following SQL server tutorial:
- SQL Server select from stored procedure
- IDENTITY_INSERT in SQL Server
- SQL Server Add Column
- SQL Server Agent won’t start
- SQL Server stored procedure output parameter
- Comparison Operators in SQL Server
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.