Working with Date and Time Data Types in SQL Server

By:   |   Updated: 2023-03-15   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Dates


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.


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:

date and time types

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:

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.

date type example

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.

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.

datetime type example

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:

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
datetime2 type example

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
smalldatetime type example

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
datetimeoffset type example

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
time type example

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
applying math operations using datetime

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
applying math operations using datetime2

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'), 
datetime2 implicit conversion to datetime

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),
implicit conversion when calculating the first day of the month

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)
Converting excel serial to date

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 categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Hadi Fadlallah 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 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

Comments For This Article

Friday, January 19, 2024 - 7:19:32 AM - Robert Back To Top (91864)
I agree with Paul your maxdate for smalldatetime is wrong but the max date is 2079-6-6 23:59:59.997, anyone can test by declaring a variable as smalldatetime and setting the variable to '2079-06-07' if will generate an out of range error for that datatype.

Monday, October 23, 2023 - 11:34:40 AM - Paul Back To Top (91699)
Your min & max values for SmallDateTime is incorrect, the min value is 1900-01-01 00:00:00:.000 and a max value of: 2079-12-31 23:59:59.997.

get free sql tips
agree to terms