SQL DATETIME Data Type

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, 23^rd 2017 at 11:30 and 34 seconds. 

Each date time value consists of several elements: 

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: 

DECLARE @currentDate DATETIME = GETDATE(); 

Converting DATETIME Values 

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

SELECT YEAR('2019-12-31') year, MONTH('2019-12-31') month 
union all 
SELECT YEAR('DECEMBER 21, 2015'), MONTH('DECEMBER 21, 2015') month 
union all 
SELECT YEAR('12-31-2017'), MONTH('12-31-2017') month 
union all 
SELECT YEAR('12/31/2016'), MONTH('12/31/2016') month 

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: 

SELECT DATEADD(m,3,'2019/4/15')

Additional Resources 

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

• Common Data Types used in SQL Server 
• How do I handle a “Error converting data type” error?
• Recommended book – SQL Queries for Mere Mortals 
• DateTime Functions 
• Datetime (TSQL)