Remove unnecessary string and Update date format in MySql

Question

Basically my date in MySql table looks like this

2001-04-16

The format is actually day-month-year. Unfortunately 20 is added to the date. So finally i should update this date. This should become

01-04-2016

How to update my all date format in my table

Possible duplicate of [Mysql: Setup the format of DATETIME to 'DD-MM-YYYY HH:MM:SS' when creating a table](http://stackoverflow.com/questions/8338031/mysql-setup-the-format-of-datetime-to-dd-mm-yyyy-hhmmss-when-creating-a-tab) — Draken, May 02 '16 at 06:05

tadman · Answer 1 · 2016-05-02T06:08:57.200

1

Don't change this in the database, change it when you need to display it. The ISO date format is what's used internally and should be kept that way, it's the most native, efficient format, and it sorts correctly.

You can select them out:

SELECT DATE_FORMAT(date_column, '%d-%m-%Y') FROM table_name

Using the DATE_FORMAT() function.

Even better is to do this in your application layer, whatever that is, using a date formatting function tuned to the user's locale.

If you've got a bunch of bad data in your database you need to convert, you can re-write it with the DATE_FORMAT() function to strip out the mistakes.

For example:

UPDATE table_name SET date_column=DATE_FORMAT('20%d-%m-%y', date_column)

edited May 02 '16 at 06:08

answered May 02 '16 at 05:55

tadman

208,517
23
234
262

This is actually returning null. I have date column type as 'DATE' – Matarishvan May 02 '16 at 06:04
My example had the arguments in the reverse order, fixed now. – tadman May 02 '16 at 06:09

score 0 · Answer 2 · answered May 02 '16 at 06:00

For mysql, MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. so you can fetch date in whatever format you want from database, please see https://dev.mysql.com/doc/refman/5.6/en/datetime.html for more info.

you can use DATE_FORMAT() function :

SELECT DATE_FORMAT('%d-%m-%Y', date) as desired_date FROM table_name where 1

Remove unnecessary string and Update date format in MySql

2 Answers2