By: Kris Wenzel   |   Updated: March 6, 2022  
Works With: 

Use the SQL Server DATETIME data type to define columns, variables, and parameters storing a date with the time of day. 

The DATETIME data type stores both the date and time.  The allowed dates span from January 1, 1753 to December 31, 9999.  The time component ranges from 00:00:00 through 23:59:59.997. 

Here is an example of a valid DATETIME value: 

2017-11-23 11:30:34 

Which represents November, 23rd 2017 at 11:30 and 34 seconds. 

Each date time value consists of several elements: 

Element Range 
YYYY Four digits from 1753 through 9999 that represent the year 
MM Two digits ranging from 1 through 12 representing the month in the specified year. 
DD Two digits ranging from 01 to 31 representing the day of the calendar month 
hh Two digits, ranging from 00 to 23, representing the hours in the day. 
mm Two digits, ranging from 00 to 59, representing the minutes within the hour. 
ss Two digits, ranging from 00 to 59, representing the seconds within minutes. 
n* Is zero to three digits, ranging from 0 to 999 – represents fractional seconds 

Definitions using SQL DATETIME 

It is pretty easy to use DATETIME values.  Let’s look at how to define columns and variables. 

Here we use DATETIME to define a ModifiedDate column within the AdventureWorks Department table: 

CREATE TABLE HumanResources.Department( 
    DepartmentID int IDENTITY(1,1) NOT NULL, 
    Name dbo.Name NOT NULL, 
    GroupName dbo.Name NOT NULL, 
    ModifiedDate datetime NOT NULL 

You can also use it to declare local variables.  In this example, a local variable is declared and assigned the current date: 


Converting DATETIME Values 

SQL Server recognizes a wide variety of date formats which both the YEAR and MONTH functions demonstrate below: 

The key to proper interpretation is to use SET DATEFORMAT to get the proper ordering of months and days withing your dates.  This is especially important if you work between US and European based servers! 

Examples using SQL DATETIME 

A good reason to store data in DateTime over varchar is to take advantage of calendar-oriented functions, such as DATEADD and DATEDIFF

For instance, use DATEADD to add days, months, or years to one date to create another. 

Use DATEDIFF to determine the length of time between two date. 

As example here we add three months to a date: 

Additional Resources 

To learn more about SQL Server data types, check out these useful resources:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}