SQL Tutorial SQL Advanced SQL Database SQL References

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 typeDescription
DATEA 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.
YEARA 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 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 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 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 SQL code 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 Date and Time Reference


5