How do I convert strings of dates?

Question

I have a column in my database which had dates incoming in the following format: H:m:s,d.M.yyyy. Because mysql couldn't resolve this format as a date, I was forced to set the type of this column as string. Fast forward a couple of months and a few thousand records of data now I managed to change the incoming data format as: yyyy-M-d H:m:s which mysql can interpret as date.

So now I want to change the old format H:m:s,d.M.yyyy to the new one yyyy-M-d H:m:s in 4 different tables on more than a few thausand records. After I do that I will just change the type of the column of string to date without any conflicts

So what I tried was:

UPDATE myTable
SET myColumn = REPLACE(myColumn , 'H:m:s,d.M.yyyy', 'yyyy-M-d H:m:s')
WHERE myColumn LIKE '%H:m:s,d.M.yyyy%'

But that didnt work, I also then tried this, but I think my synthax is off:

UPDATE myTable

SET myColumn = REPLACE(STR_TO_DATE(myColumn , 'H:m:s,d.M.yyyy', 'yyyy-M-d H:m:s'))
WHERE myColumn LIKE '%H:m:s,d.M.yyyy%'

Doesa anyone have any suggestions or alternate solutions?

Look at this .may be its as per your requirement http://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql — Ankit Agrawal, Feb 12 '16 at 09:29
@User that guy is trying to select it, what I want is to convert it... — Vrankela, Feb 12 '16 at 09:38
You should store dates in standard MYSQL DateTime format. You only need to amend the format on presentation — RiggsFolly, Feb 12 '16 at 09:42

score 1 · Accepted Answer · answered Feb 12 '16 at 09:39

1

Add a new date column to your table, which has datetime data type.
Update the new column with the reformatted dates:

UPDATE myTable SET newdatecolumn=STR_TO_DATE(myColumn , '%H:%i:%s,%d.%M.%Y')

Check if all dates are converted successfully by checking for null values in the new column. This is why you should have a new column and no do the conversion in place.
If all ok, then remove the column with textual dates and rename the new column to the old one's name.
Modify your insert / update code to use str_to_date() when you insert / update dates in that table.

answered Feb 12 '16 at 09:39

Shadow

33,525
10
51
64

Thank you for taking your time and actually helping me with the problem instead of ridiculing my lack of knowledge :D Your approach makes sense but it doesn't work for the following reason `Incorrect datetime value: '8:0:0,25.12.2015' for function str_to_date` I even tried chaning the format you wrote from `%H:%i:%s,%d.%M.%Y` to `%H:%m:%s,%d.%M.%yyyy` and still no luck :( – Vrankela Feb 12 '16 at 09:52
It's not the year part that is at odds with the pattern, but the time part and month. Check https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format for the format string to be used. You have no leading 0s in time, perhaps not even in the date. You can see your own data, check it. – Shadow Feb 12 '16 at 10:05
Once again thank you very much for your help. This site needs more people like you:) My final format was: `%k:%i:%s,%e.%c.%Y` – Vrankela Feb 12 '16 at 10:56
Thanks, However, you truly **could** have checked the documentation on str_to_date() before using it. – Shadow Feb 12 '16 at 10:59

How do I convert strings of dates?

1 Answers1