MySQL Date Format in DD/MM/YYYY in SELECT Query

By default, date and datetime values are stored in MySQL databases in YYYY-MM-DD format. But often we need this information in a different format. For example, you may need to display date as DD/MM/YYYY format in SELECT query’s result. In this article, we will learn how to easily format your date columns in MySQL. We will learn how to set MySQL date format in DD/MM/YYYY in SELECT query.

MySQL Date Format in DD/MM/YYYY in SELECT Query

Let us say you have the following MySQL table sales(id, order_date, amount).

mysql> create table sales(id int, order_date date, amount int);

mysql> insert into sales(id, order_date, amount) 
values(1, '2022-01-01', 150),
(2,'2022-01-02',180),
(3,'2022-01-03',300);

mysql> select * from sales;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2022-01-01 |    150 |
|    2 | 2022-01-02 |    180 |
|    3 | 2022-01-03 |    300 |
+------+------------+--------+

As you can see the dates in order_date column are stored as YYYY-MM-DD format.

Now if you want to format this date as DD-MM-YYYY then you need to use date_format() function that allows you to format a given date literal or column name in a format of your choice.

Here is the syntax of date_format() function.

date_format(date, format);

Here are a couple of examples to use date_format() function.

select date_format('2022-03-04','%d/%m/%Y');
04/03/2022

select date_format(order_date,'%d/%m/%Y');
+------------------------------------+
| date_format(order_date,'%d/%m/%Y') |
+------------------------------------+
| 01/01/2022                         |
| 02/01/2022                         |
| 03/01/2022                         |
+------------------------------------+

Here is another example to use date_format() function in your SQL query.

 SELECT id, DATE_FORMAT(order_date,'%d/%m/%Y') AS niceDate, amount
       FROM sales;
+------+------------+--------+
| id   | niceDate   | amount |
+------+------------+--------+
|    1 | 01/01/2022 |    150 |
|    2 | 02/01/2022 |    180 |
|    3 | 03/01/2022 |    300 |
+------+------------+--------+

MySQL date format supports a large number of date formats. Here is a list of format specifiers to help you customize the date formats as per your requirements.

You can also use different characters with format specifiers to generate your date string. For example, although %Y is the format specifier for year format YYYY and %m is the format specifier for month and %d is the format specifier for date, we use them in conjunction with ‘/’ character to generate our date string. E.g. ‘%d/%m/%Y.

MySQL Date Format in DD/MM/YYYY in SELECT Query

MySQL Date Format in DD/MM/YYYY in SELECT Query

Related posts:

Leave a Reply Cancel reply