In MySQL, how can we display the date in other format specified by the user?

We need to use DATE_FORMAT() function to display the date in other formats. There would be two arguments of this function, first would be the date and second would be the format string.

Example − Suppose in the table ‘date_testing we have three dates in the following format

mysql> Select * from date_testing;

+------------+
| Date       |
+------------+
| 2017-03-15 |
| 2017-03-25 |
| 2017-04-05 |
+------------+
3 rows in set (0.00 sec)

Now DATE_FORMAT() function will change the format of the above dates in the format given by the user as follows −

mysql> Select DATE_FORMAT(Date, '%W %D %M %Y')AS 'FORMATTED DATE' from date_testing;

+---------------------------+
| FORMATTED DATE            |
+---------------------------+
| Wednesday 15th March 2017 |
| Saturday 25th March 2017  |
| Wednesday 5th April 2017  |
+---------------------------+
3 rows in set (0.00 sec)

Here in the above example %W, %D, etc. are date format characters.

Daniol Thomas

Updated on: 2020-06-20T05:54:16+05:30

110 Views

Kickstart Your Career

Get certified by completing the course

Get Started