Convert MySQL data dd / mm / yyyy to yyyy-mm-dd

Question

Convert MySQL data dd / mm / yyyy to yyyy-mm-dd

Navigation

#1 by (6 votes)
#2 by (1 votes)

2

I have a database where I inserted the dates in the format dd/mm/yyyy (example: 05/11/1987 ), but now I'm organizing reports and I need to update all the dates in the database to the format yyyy-mm-dd 1987-11-05 ). How do I do this in MySQL?

Remembering that this is not a duplicate, since I am wanting to update the data in the database and not select them in another format.

mysql date

asked by anonymous 30.06.2015 / 18:39

2 answers

1

You can make a string turn a date only with the functions available in mysql as str_to_time () and then use date_format () to change the format.

SELECT date_format(str_to_date('30/01/2015', '%d/%m/%Y'), '%Y-%m-%d')

30.06.2015 / 18:48

Problem with access limit by refresh account JAVASCRIPT Check UNIQUE element before inserting it

score 6 · Accepted Answer

Cristiano , here's what you need:

UPDATE tbl_data SET data =
    DATE_FORMAT(STR_TO_DATE(data, '%d/%m/%Y'), '%Y-%m-%d') 
WHERE data LIKE '__/__/____'

Before

23/11/1987

Then

1987-11-23