Learn about SQL Date Data Types – Date, DateTime, DateTime2, Time

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.

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.

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