SQL Server DATETIME Data Type
Use the SQL Server DATETIME data type to define columns, variables, and parameters storing a date with 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:
Which represents November, 23rd 2017 at 11:30 and 34 seconds.
Each date time value consists of several elements:
|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:
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:
To learn more about SQL Server data types, check out these useful resources: