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

Asked

Viewed 28,630 times

3

I have a database where I entered the dates in the format dd/mm/yyyy (example: 05/11/1987), but now I’m organizing some reports and need to update all 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, because I want to update the data in the database and not select them in another format.

mysql date

  • You need to update this data to this new format or bring (SELECT) in yyyy-mm-dd format?

  • Good afternoon @Felipedouradinho, so I need to update this data to the new format.

  • Maicon, it’s not duplicate.

  • Is your field date? Or varchar? What does PHP have to do with this question?

  • 2

    @Kaduamaral the answer of Felipe Douradinho solved here my problem the field and varchar but for me to change it to date had to make this update of the information otherwise I would lose them

  • Correct @Cristianocardososilva, good that you have. ;) .

Show 1 more comment

2 answers

10


Cristiano, here 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

Afterward

1987-11-23

2

You can make a string into a date only with the functions available in mysql like 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')

  • He wants to update (UPDATE), not select (SELECT)

  • I already updated my answer but idea is the same. Thanks for the correction @Felipedouradinho

  • Really, I hadn’t noticed! They’re both for posterity so, hehe

  • wasn’t what the guy wanted, but it served me very much. thanks.

Browser other questions tagged mysql date

You are not signed in. Login or sign up in order to post.