CODEWITHSUNDEEP
mysqldate
blankip
blankip

Reputation: 340

str_to_date driving me nuts in mysql

UPDATE `smart_userstest`
SET lastlogin = STR_TO_DATE(lastlogin,'%d/%m/%y %h:%i')
WHERE lastlogin REGEXP('^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$')

Have a query that I have used before... Except now I am getting errors.

So I have imported excel spreadsheets hitting my DB. Lots of date fields. Most are in a new format which I am trying to fix. m/d/yyyy hour:min where month, day, hour, and min can be 1-2 numbers. I would like to simply get rid of the hour:min portion and set the date to a proper MySql DATE. Also I would like it to account for the hour:min portion being left blank because I have both.

Example error: 1411 - Incorrect datetime value: '1/24/2014' for function str_to_date

Upvotes: 0

Views: 296

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can solve this with a case:

UPDATE `smart_userstest`
    SET lastlogin = (case when lastlogin like '%/%/% %:%' then STR_TO_DATE(lastlogin,'%d/%m/%y %h:%i')
                          when lastlogin like '%/%/%' then STR_TO_DATE(lastlogin,'%d/%m/%y')
                     end)
    WHERE lastlogin REGEXP('^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$');

To me, it is strange that you are setting a character column to a date.

A better approach is to load the data into a staging table, smart_userstest_staging. All the columns are varchar(). There should be no errors loading it in.

Then create smart_userstest with logic on each field to convert it to the right value or check that the values are correct (often, you will just copy them in). This gives you the ability to easily find bad data in the original table and greatly helps with fixing problems.

Modifying the data in place prevents you from having an audit trail of what you had to do to get everything working. That means that the next time you have to do the task, you have to relearn everything over again. If you load into a staging table, the logic is all encapsulated in a SQL query that transfers data from the staging table to the real table.

EDIT:

Try this:

UPDATE `smart_userstest`
    SET lastlogin = STR_TO_DATE(substring_index(lastlogin, ' ', 1),'%d/%m/%y')
    WHERE lastlogin REGEXP('^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$');

This chooses just the m/d/yyyy portion of the string. By the way, I don't see how the string with the hour/minute is passing the REGEXP pattern. I think the problem might be somewhere else.

Upvotes: 1

Related Questions