SQL - DATES

While working with 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.

MySQL Date formats

The different data types available in MySQL for storing a date or a date/time value are mentioned below:

Data type	Description
DATE	A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'.
DATETIME(fsp)	A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time.
TIMESTAMP(fsp)	A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition.
YEAR	A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. MySQL 8.0 does not support year in two-digit format.

SQL Server Date formats

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

Data type	Description
DATETIME	A 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.
DATETIME2	A 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.
DATE	A date. Format: 'YYYY-MM-DD'. It supports range from January 1, 0001 to December 31, 9999.
TIME	A 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'.
DATETIMEOFFSET	A 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.
SMALLDATETIME	A 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 SQL code 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
);

This will create a empty table named Employee containing five columns.

Example: Working with date datatype

Consider a database table called Employee with the following records:

EmpID	Name	City	Age	Date_of_Joining
1	John	London	25	2019-05-25
2	Marry	New York	24	2018-10-15
3	Jo	Paris	27	2017-06-09
4	Kim	Amsterdam	30	2014-04-21
5	Ramesh	New Delhi	28	2019-05-25
6	Huang	Beijing	28	2020-01-10

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

SELECT * FROM Employee
WHERE Date_of_Joining = '2019-05-25';

This will produce the result as shown below:

EmpID	Name	City	Age	Date_of_Joining
1	John	London	25	2019-05-25
5	Ramesh	New Delhi	28	2019-05-25

Complete Date and Time Reference

MySQL Data and Time Reference - See for a complete reference of all MySQL Date and Time functions.
SQL Server Data and Time Reference - See for a complete reference of all SQL Server Date and Time functions.
Oracle Data and Time Reference - See for a complete reference of all Oracle Date and Time functions.