sql

Date Format in SQL

In this article, we will explain the Date Format in SQL.

1. Introduction

sql date format

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

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button