By: Hadi Fadlallah | Updated: 2023-03-15 | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Dates
Problem
SQL Developers and DBAs use different date data types, such as DATE, DATETIME, and DATETIME2, to store datetime values. In contrast, each one of these types has a use case which we will cover in this T-SQL tutorial.
Solution
This tutorial explains different data types that can be used to store date time values and illustrates when to use each one in scripts, stored procedures and more.
Date and Time Data Types in SQL Server
There are six data types in a SQL database that store date and time values:
Before explaining the date data types, the following table shows the different characters used within date formats.
| Characters | Description | Example | Value |
|---|---|---|---|
| y | Year | yyyy | 2010 |
| M | Month | yyyy-MM | 2010-12 |
| d | Day | yyyy-MM-dd | 2010-12-31 |
| H | Hour (24-hour format) | yyyy-MM-dd HH | 2010-12-31 12 |
| h | Hour (12-hour format) | yyyy-MM-dd hh | 2010-12-31 12 |
| m | Minutes | yyyy-MM-dd HH:mm | 2010-12-31 12:59 |
| s | Seconds | yyyy-MM-dd HH:mm:ss | 2010-12-31 12:59:59 |
| f | Milliseconds fraction | yyyy-MM-dd HH:mm:ss.fff | 2010-12-31 12:59:59.110 |
| tt | AM/PM designator | yyyy-MM-dd hh:mm tt | 2010-12-31 12:59 PM |
SQL Date Data Type
The date data type is used to store only dates without the time. It comprises three main parts: the year, month, and day. This data type ranges from 0001-01-01 through 9999-12-31. The default format of a date value is yyyy-MM-dd.
Let's try the following SQL command:
DECLARE @date DATE SET @date = '2009-01-01 10:00:00' SELECT @date as date_value
As shown in the screenshot below, executing this query results in a date value without the time portion.
By default, the date data type accepts date values stored within a string if they have an acceptable format, for example: "yyyy-MM-dd" or "yyyyMM".
SQL Datetime Data Type
The datetime data type is used to store the date and time value. This data type ranges from 1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997 and allows storing three milliseconds fractions and the third fraction is rounded to 0, 3, or 7. The default format of a datetime value is yyyy-MM-dd HH:mm:ss.fff.
DECLARE @date DATETIME SET @date = '2009-01-01 10:00:00.122' SELECT @date as datetime_value
This query results in the following value 2009-01-01 10:00:00.123, as shown below.
SQL Datetime2 Data Type
The datetime2 data type was introduced in SQL Server 2008. It can be considered an extension of the datetime data type as follows:
- It has a larger date range; it ranges from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999
- It supports a larger default fractional precision (7 digits)
- It allows the user to specify the fractional precision.
The following SQL query shows the default fractional precision of 7 compared to a user-specified precision of 5:
DECLARE @date DATETIME2, @date5 DATETIME2(5) SELECT @date5 = '2009-01-01 10:00:00.122' , @date = '2009-01-01 10:00:00.122' SELECT @date as datetime2_value, @date5 as datetime2_value5
SQL Smalldatetime Data Type
As the name implies, the smalldatetime data type requires less storage space than the other datetime types. Also, it does not store the seconds or the fractional part. This data type ranges from 0001-01-01 00:00 to 9999-12-31 23:59.
A smalldatetime is often used for an application that does not require a high time precision, such as weather forecasting applications.
For instance, executing the syntax below shows that seconds and fractional values are ignored.
DECLARE @date smalldatetime SET @date = '2009-01-01 10:00:10.122' SELECT @date as smalldatetime_value
SQL Datetimeoffset Data Type
The datatimeoffset data type can be considered an extension for the datetime2 type that adds time zone awareness based on UTC (Universal Time Coordinate or Greenwich Mean Time). This data type ranges from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999. Besides, the time offset ranges from -14:00 through +14:00.
As shown below, this data type supports a user-defined fractional precision and always shows the time offset next to the date and time values.
DECLARE @date datetimeoffset(5) SET @date = '2009-01-01 10:00:10.122 +02:00' SELECT @date as datetimeoffset_value
SQL Time Data Type
For some specific reason, several applications require that time is stored separately. The time data type allows storing time values ranging from 00:00:00.0000000 to 23:59:59.9999999. It has a seven fractional precision by default and supports user-defined precisions.
DECLARE @time time(5) SET @time = '2009-01-01 10:00:10.122' SELECT @time as time_value
What About Timestamp?
The timestamp data type has nothing to do with dates. This datatype is also known as "rowversion". The timestamp column is automatically updated whenever the row is updated, and the value is monotonically increasing in the database. This data type may help implement data change capture logic in a data warehousing project.
Comparing SQL Server Date Time Data Types
Here is a table that compares the different data types.
| Type | Storage Size | Stores Date | Stores Time | Default Format | Precision | Min Value | Max Value |
|---|---|---|---|---|---|---|---|
| Date | 3 bytes | Yes | No | yyyy-MM-dd | 1 day | 0001-01-01 | 9999-12-31 |
| Datetime | 8 bytes | Yes | Yes | yyyy-MM-dd HH:mm:ss.fff | Values are rounded to .000, 003, or 007 milliseconds | 1753-01-01 00:00:00.000 | 9999-12-31 23:59:59.997 |
| Datetime2 | 6-8 bytes | Yes | Yes | yyyy-MM-dd HH:mm:ss.fffffff | 100 nanoseconds | 0001-01-01 00:00:00.0000000 | 9999-12-31 23:59:59.9999999 |
| Smalldatetime | 4 bytes | Yes | Yes | yyyy-MM-dd HH:mm | 1 minute | 0001-01-01 00:00 | 0001-01-01 23:59 |
| Datetimeoffset | 10 bytes | Yes | Yes | yyyy-MM-dd HH:mm:ss.fffffff {+|-}hh:mm | 100 nanoseconds | 0001-01-01 00:00:00.0000000 | 9999-12-31 23:59:59.9999999 |
| Time | 5 bytes | No | Yes | HH:mm:ss.fffffff | 100 nanoseconds | 00:00:00.0000000 | 23:59:59.9999999 |
SQL Datetime vs. SQL Datetime2
Many developers ask why the datetime data type is used when datetime2 supports a broader range and can use less storage space. There are several shortcomings in using the datetime2 type.
Mathematic Operations Not Supported
One of the main problems is the lack of capability to do basic math operations with dates. This includes calculating the difference between two dates in days, hours, and minutes. For example, let's try to increment a date value stored within a datetime variable.
DECLARE @date datetime = '19000101' SELECT @date1 + 1
If we repeat the same experiment using a datetime2 value, the following error shows up: "Operand type clash: datetime2 is incompatible with int"
DECLARE @date datetime2(0) = '19000101' SELECT @date1 + 1
Implicit Conversion Problem
Once a datetime2 value needs to be compared with a constant value or another date type, an implicit conversion occurs. This causes a performance hit.
For example, the following SQL command execution results show that the returned value is datetime.
DECLARE @date datetime2(0) = '20100101'
SELECT DATEADD(dd,DATEDIFF(dd,'1900-01-01',@date),'1900-01-01'),
SQL_VARIANT_PROPERTY(DATEADD(dd,DATEDIFF(dd,'1900-01-01',@date),'1900-01-01'),'BASETYPE')
Or another example is if we need to calculate the first day of the month. This will also cause an implicit conversion.
DECLARE @date datetime2(0) = '20100109'
SELECT DATEADD(mm,DATEDIFF(mm,0,@date),0),
SQL_VARIANT_PROPERTY(DATEADD(mm,DATEDIFF(mm,0,@date),0),'BASETYPE')
Statistics Problem
Using the datetime2 type may lead to a wrong query plan estimation. This is because SQL Server can't use statistics properly for datetime2 columns due to the way data is stored. You can learn more about this issue in the following article: Performance Surprises and Assumptions : DATEADD.
Converting Excel Date Number to a SQL Date
A common issue occurs when importing data from Excel sheets into SQL Server. Date values in Excel appear as numeric values. As stated by Microsoft, "Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900."
Once these values are in SQL Server, we can use the dateadd() function to convert serial values to date values or cast those values as smalldatetime data type.
If we enter January 1, 2008 in Excel, the numeric value is 39448, so the code below shows how to convert 39448 back to a date in SQL Server.
SELECT DATEADD(d,39448 ,'1899-12-30') SELECT CAST(39448 - 2 as SmallDateTime)
The reason to subtract two days or assume that 1899-12-30 is the base date is that Excel incorrectly assumes that the year 1900 is a leap year.
Next Steps
After finishing this tip, it is recommended to read more about date and time functions in SQL Server:
- SQL Server Date and Time Functions with Examples
- Date and Time Conversions Using SQL Server
- SQL Server DATEPART Function
- Determine SQL Server Date and Time Parts with DATEPART and DATENAME Functions
- How to Get Current Date in SQL Server with GETDATE()
- SQL DATE Functions
About the author
Hadi Fadlallah is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com. He holds a Ph.D. in data science focusing on context-aware big data quality and two master's degrees in computer science and business computing. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-03-15
