Syntax error Best way to change the date format in MySQL SELECT?

Best way to change the date format in MySQL SELECT?

The best way to change the date format in MySQL SELECT is as follows

SELECT DATE_FORMAT(yourColumnName, "%d/%m/%Y %H:%i") AS anyAliasName FROM yourTableName WHERE yourCondition;

To understand the above concept, let us create a table. The query to create a table is as follows

mysql> create table bestDateFormatDemo
   - > (
   - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   - > ArrivalDateTime datetime
   - > );
Query OK, 0 rows affected (0.64 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into bestDateFormatDemo(ArrivalDateTime) values(now());
Query OK, 1 row affected (0.11 sec)
mysql> insert into bestDateFormatDemo(ArrivalDateTime) values(date_add(now(),interval -2
year));
Query OK, 1 row affected (0.33 sec)
mysql> insert into bestDateFormatDemo(ArrivalDateTime) values('2014-01-31 13:45:56');
Query OK, 1 row affected (0.15 sec)
mysql> insert into bestDateFormatDemo(ArrivalDateTime) values('2016-02-11 12:40:30');
Query OK, 1 row affected (0.17 sec)
mysql> insert into bestDateFormatDemo(ArrivalDateTime) values('2018-12-31 12:59:59');
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from bestDateFormatDemo;

The following is the output

+----+---------------------+
| Id | ArrivalDateTime     |
+----+---------------------+
|  1 | 2019-02-22 22:50:10 |
|  2 | 2017-02-22 22:50:28 |
|  3 | 2014-01-31 13:45:56 |
|  4 | 2016-02-11 12:40:30 |
|  5 | 2018-12-31 12:59:59 |
+----+---------------------+
5 rows in set (0.00 sec)

Here is the query to change the date format in MySQL select statement

mysql> SELECT DATE_FORMAT(ArrivalDateTime, "%d/%m/%Y %H:%i")AS
changeDateFormat FROM bestDateFormatDemo WHERE Id IN(1,2,3,4,5);

The following is the output

+------------------+
| changeDateFormat |
+------------------+
| 22/02/2019 22:50 |
| 22/02/2017 22:50 |
| 31/01/2014 13:45 |
| 11/02/2016 12:40 |
| 31/12/2018 12:59 |
+------------------+
5 rows in set (0.28 sec)

If you want the hour in 12-hour format, use ‘h’ instead of ‘H’.

The query is as follows

mysql> SELECT DATE_FORMAT(ArrivalDateTime, "%d/%m/%Y %h:%i")AS
changeDateFormat FROM bestDateFormatDemo WHERE Id IN(1,2,3,4,5);

The following is the output

+------------------+
| changeDateFormat |
+------------------+
| 22/02/2019 10:50 |
| 22/02/2017 10:50 |
| 31/01/2014 01:45 |
| 11/02/2016 12:40 |
| 31/12/2018 12:59 |
+------------------+
5 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:25+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements