SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

SQL Server - DATES



While working with SQL Server (Transact-SQL) Date datatypes, it is essential to know about the dates format when performing different operations on a database. Please note that, the datatype of column must be chosen while creating a new table in the database.

The different data types available in SQL Server (Transact-SQL) for storing a date or a date/time value are mentioned below:

Data typeDescription
DATETIMEA date and time combination. Format: 'YYYY-MM-DD hh:mm:ss[.mmm]'. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds.
DATETIME2A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss[.fractional seconds]'. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.
DATEA date. Format: 'YYYY-MM-DD'. It supports range from January 1, 0001 to December 31, 9999.
TIMEA time. Format: 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]'. Used to store time only to an accuracy of 100 nanoseconds. Values range from '00:00:00.0000000' to '23:59:59.9999999'.
DATETIMEOFFSETA date and time combination and adds time zone awareness based on UTC (Universal Time Coordinate or Greenwich Mean Time). Format: 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' [{+|-}hh:mm]. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.
SMALLDATETIMEA date and time combination. Format: 'YYYY-MM-DD hh:mm:ss'. It supports range from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute.

Example: Create a table with date datatype

The below mentioned query creates a table called Employee which contains five columns: EmpID, Name, City, Age and Date of Joining.

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  [Date of Joining] DATE FORMAT 'YYYY-MM-DD'
);

This will create a empty table named Employee containing five columns as shown below:

EmpIDNameCityAgeDate of Joining

Example: Working with date datatype

Consider a database table called Employee with the following records:

EmpIDNameCityAgeDate of Joining
1JohnLondon252019-05-25
2MarryNew York242018-10-15
3JoParis272017-06-09
4KimAmsterdam302014-04-21
5RameshNew Delhi282019-05-25
6HuangBeijing282020-01-10

To fetch data of all employees present in the Employee table who joined the organization on '2019-05-25', the query is:

SELECT * FROM Employee
WHERE [Date of Joining] = '2019-05-25';

This will produce the result as shown below:

EmpIDNameCityAgeDate of Joining
1JohnLondon252019-05-25
5RameshNew Delhi282019-05-25

Complete SQL Server Date and Time Reference

For a complete reference of all SQL Server Date and Time functions, see the SQL Server Data and Time Reference.


5