This article explores the main differences between the datetime and datetime2 data types in SQL Server.
If you’re not sure which one to use, use datetime2 (see its advantages below).
Category: DBMS
Database Management Systems
Understanding ‘time’ Storage Size in SQL Server
In this article I look at the storage size of the time data type in SQL Server.
In particular, I look at the following:
- Microsoft’s documentation
- Data stored in a variable
- Length in bytes using
DATALENGTH() - Length in bytes using
DATALENGTH()after converting to varbinary
- Length in bytes using
- Data stored in a database
- Length in bytes using
COL_LENGTH() - Length in bytes using
DBCC PAGE()
- Length in bytes using
Understanding ‘datetimeoffset’ Storage Size in SQL Server
In this article I look at how the datetimeoffset data type is stored in SQL Server, and how you can get different reported storage size results, depending on what you’re doing with it.
This is similar to what I did with the datetime2 data type.
In particular, I look at the following:
- Microsoft’s documentation
- Data stored in a variable
- Length in bytes using
DATALENGTH() - Length in bytes using
DATALENGTH()after converting to varbinary
- Length in bytes using
- Data stored in a database
- Length in bytes using
COL_LENGTH() - Length in bytes using
DBCC PAGE()
- Length in bytes using
Understanding ‘datetime2’ Storage Size in SQL Server
In this article I share some observations I’ve had regarding the datetime2 data type’s storage size in SQL Server. Perhaps I will clarify some points about the actual storage size used by this data type when stored in a database.
In particular, I look at the following:
- Microsoft’s documentation
- Data stored in a variable
- Length in bytes using
DATALENGTH() - Length in bytes using
DATALENGTH()after converting to varbinary
- Length in bytes using
- Data stored in a database
- Length in bytes using
COL_LENGTH() - Length in bytes using
DBCC PAGE()
- Length in bytes using
Some of those seem to contradict each other, and you will see two different storage size amounts for the same value, depending on where you look.
Comparison of the Date & Time Data Types in SQL Server
This article provides a side-by-side comparison of the date and time data types in SQL Server regarding their range, accuracy, and storage size.
Converting Between Date & Time Data Types in SQL Server (T-SQL Examples)
When you convert between date and time data types in SQL Server, you need to be mindful of how the new data type will handle the value you’re trying to assign to it.
In some cases you might lose part of the value, in other cases you might gain a bunch of zeros (increasing storage size in the process). You may also end up with a value that’s been rounded up.
The following articles contain examples of conversions between the different date and time data types, with a particular focus on the issues I just mentioned.
Convert ‘datetimeoffset’ to ‘time’ in SQL Server (T-SQL Examples)
If you have a datetimeoffset value, but you don’t need the date and time zone offset part, converting it to time will save you a lot of storage space (while removing unnecessary details from the value). This article contains examples of converting a datetimeoffset value to a time value in SQL Server.
Convert ‘datetimeoffset’ to ‘date’ in SQL Server (T-SQL Examples)
If you have a datetimeoffset value, but you don’t need the time and time zone offset part, converting it to date will save you a lot of storage space (while removing unnecessary details from the value). This article contains examples of converting a datetimeoffset value to a date value in SQL Server.
Convert ‘datetimeoffset’ to ‘smalldatetime’ in SQL Server (T-SQL Examples)
This article contains examples of converting a datetimeoffset value to smalldatetime in SQL Server.
When you convert a datetimeoffset value to smalldatetime, the date and hours are copied. The minutes are rounded up (depending on the seconds value) and the seconds are set to 0.
Convert ‘datetimeoffset’ to ‘datetime2’ in SQL Server (T-SQL Examples)
This article contains examples of converting a datetimeoffset value to datetime2 in SQL Server.
When you convert a datetimeoffset value to datetime2, the date and time are copied to the datetime2 value, and the time zone is truncated. The fractional seconds are also truncated to fit if the destination precision is lower.