Search code examples
sqlmysqldateformatsql-update

Changing data from DD/MM/YYYY to mySQL format YYYY-MM-DD

I am currently trying to get the data that I loaded from a .txt file to show the data in a way that I can manipulate it via excel's YEAR(function). The raw data date format looks like this:

01/03/2018

I have it loaded in as a VARCHAR at the moment so that the date at least shows in my data. I would like to convert it to:

2018-01-03

My current method is:

UPDATE Schema.Table
SET Admit_Date = str_to_date(Admit_Date, '%d/%m/%Y')

But I get the following error

Error Code: 1411. Incorrect datetime value: 'Admit_Date' for function str_to_date

I have looked this up but with no success to my specific issue making me think that this is a issue with how I have my table set up. Can someone help with my problem? Thanks!

Solution

  • Try '%m/%d/%Y'. I believe some of your data is making this mistake where it is reading the days as months.