In this article, we will explain the Date Format in SQL.
1. Introduction
Structured Query Language (SQL) defines the date and time data types. In this example, I will use the MySQL database to demonstrate both date data types and corresponding utility functions.
- DATE – stores a calendar date in the YYYY-MM-DD format. The valid date ranges from ‘0000-01-01‘ to ‘9999-12-31′.
- TIME – stores a clock value in the HH:MM:SS format. The valid time ranges from ‘00:00:00‘ to ‘23:59:59‘.
- TIMESTAMP – stores the date and time value in the YYYY-MM-DD HH:MM:SS format. The valid timestamp ranges from ‘1970-01-01 00:00:01‘ UTC to ‘2038-01-19 03:14:07‘ UTC.
2. Technologies Used
The example code in this article was built and run using:
- MySQL
3. Create a Table
I will create a DEMO_DATE_TBL table which has several columns with date, time, and timestamp data types.
create table
create table DEMO_DATE_TBL( USER_ID INT NOT NULL AUTO_INCREMENT, USER_NAME VARCHAR(100) NOT NULL, BIRTH_DATE DATE NOT NULL, CREATED_ON TIMESTAMP NOT NULL, SHIFT_START TIME NOT NULL, SHIFT_END TIME NOT NULL, PRIMARY KEY ( user_id ) );
Insert several valid records.
valid insert statements
insert into demo_date_tbl(user_name, birth_date, CREATED_ON, SHIFT_START, SHIFT_END) values('Mary Zheng', '1970-12-01', now(), '08:00:00', '17:00:00');
insert into demo_date_tbl(user_name, birth_date, CREATED_ON, SHIFT_START, SHIFT_END) values('Tom Zheng', '1971-12-11', now(), '08:00:00', '17:00:00');
insert into demo_date_tbl(user_name, birth_date, CREATED_ON, SHIFT_START, SHIFT_END) values('Amy Zheng', '1970-12-13', now(), '08:00:00', '17:00:00');
insert into demo_date_tbl(user_name, birth_date, CREATED_ON, SHIFT_START, SHIFT_END) values('Mary Zhang', '1971-12-01', now(), '08:00:00', '17:00:00');
insert into demo_date_tbl(user_name, birth_date, CREATED_ON, SHIFT_START, SHIFT_END) values('Job Zhang', '1971-12-11', now(), '08:00:00', '17:00:00');
insert into demo_date_tbl(user_name, birth_date, CREATED_ON, SHIFT_START, SHIFT_END) values('Anne Zhang', '1970-12-13', now(), '08:00:00', '17:00:00');
It will encounter an error message when the data is not in the valid range.
invalid insert statements
mysql> insert into demo_date_tbl( user_name , birth_date, CREATED_ON, SHIFT_START, SHIFT_END) values('out-of-range', '10000-
12-13', now(), '08:00:00', '17:00:00');
ERROR 1292 (22007): Incorrect date value: '10000-12-13' for column 'birth_date' at row 1
mysql>
mysql> insert into demo_date_tbl( user_name , birth_date, CREATED_ON, SHIFT_START, SHIFT_END) values('out-of-range', '1900-12-13', '1960-01-01', '08:00:00', '17:00:00');
ERROR 1292 (22007): Incorrect datetime value: '1960-01-01' for column 'CREATED_ON' at row 1
mysql>
mysql> insert into demo_date_tbl( user_name , birth_date, CREATED_ON, SHIFT_START, SHIFT_END) values('out-of-range', '1900-12-13', '2960-01-01', '08:00:00', '17:00:00')
-> ;
ERROR 1292 (22007): Incorrect datetime value: '2960-01-01' for column 'CREATED_ON' at row 1
mysql>4. Date and Time Functions
MySQL provides a list of date and time functions to transform the date and time data. In this step, I will demonstrate the following common date functions.
- DATE_FORMAT – the date type is formatted as YYYY-MM-DD, so we will use the date_format function to transform to a different format.
- MONTH – it extracts the month value from either DATE or TIMESTAMP column.
- MAKEDATE – it creates a date from a given year and number of days in a year.
- CURRENT_TIME – it returns the current time.
date_format
mysql> select birth_date, date_format(birth_date, '%m-%d-%Y') from demo_date_tbl; +------------+-------------------------------------+ | birth_date | date_format(birth_date, '%m-%d-%Y') | +------------+-------------------------------------+ | 1970-12-01 | 12-01-1970 | | 1971-12-11 | 12-11-1971 | | 1970-12-13 | 12-13-1970 | | 1971-12-01 | 12-01-1971 | | 1971-12-11 | 12-11-1971 | | 1970-12-13 | 12-13-1970 | +------------+-------------------------------------+ 6 rows in set (0.00 sec) mysql>
month
mysql> select month(birth_date) birth_month, month(created_on) create_month from demo_date_tbl; +-------------+--------------+ | birth_month | create_month | +-------------+--------------+ | 12 | 9 | | 12 | 9 | | 12 | 9 | | 12 | 9 | | 12 | 9 | | 12 | 9 | +-------------+--------------+ 6 rows in set (0.00 sec)
makedate
mysql> select makedate(2021, 09) ; +--------------------+ | makedate(2021, 09) | +--------------------+ | 2021-01-09 | +--------------------+ 1 row in set (0.00 sec) mysql> select makedate(2021, 90) ; +--------------------+ | makedate(2021, 90) | +--------------------+ | 2021-03-31 | +--------------------+ 1 row in set (0.00 sec) mysql>
current_time
mysql> select * from demo_date_tbl where SHIFT_START > current_time(); +---------+------------+------------+---------------------+-------------+-----------+ | user_id | user_name | birth_date | CREATED_ON | SHIFT_START | SHIFT_END | +---------+------------+------------+---------------------+-------------+-----------+ | 1 | Mary Zheng | 1970-12-01 | 2021-09-13 02:27:40 | 08:00:00 | 17:00:00 | | 2 | Tom Zheng | 1971-12-11 | 2021-09-13 02:27:40 | 08:00:00 | 17:00:00 | | 3 | Amy Zheng | 1970-12-13 | 2021-09-13 02:27:40 | 08:00:00 | 17:00:00 | | 4 | Mary Zhang | 1971-12-01 | 2021-09-13 02:27:40 | 08:00:00 | 17:00:00 | | 5 | Job Zhang | 1971-12-11 | 2021-09-13 02:27:40 | 08:00:00 | 17:00:00 | | 6 | Anne Zhang | 1970-12-13 | 2021-09-13 02:27:40 | 08:00:00 | 17:00:00 | +---------+------------+------------+---------------------+-------------+-----------+ 6 rows in set (0.00 sec) mysql>
5. Summary
In this article, we explained the Date Format in SQL via the create table command with date and time data types and the utility functions to format a date and parse the date parts.
Database servers provide date and time data types and corresponding functions. Please click SQL server date and time data types for SQL Server database and here for Oracle database.
6. Download the Source Code
Download
You can download the full source code of this example here: Date Format in SQL
You can download the full source code of this example here: Date Format in SQL
