Search code examples
mysqldatestr-to-date

Convert MySQL text string with time to date

I'm trying to convert a date string with time (stored in a varchar field) to a date format. The current strings are in this format: December 23, 2010 13:57

What I need them to be converted to is a standard date format without the time: 2010-12-23

I've tried the following but come up with NULL for the results:

SELECT STR_TO_DATE(fieldname, '%Y-%m-%d') AS newdate FROM tablename;

Next, I thought maybe I'd have to strip the time off of the string, so I tried:

SELECT STR_TO_DATE(SUBSTRING(fieldname, 1, CHAR_LENGTH(fieldname) - 6), '%Y-%m-%d') AS newdate FROM tablename;

and also got NULL for the results.

Am I approaching this incorrectly?

Solution

  • The correct format is this:

    SELECT STR_TO_DATE(fieldname, '%M %e, %Y') AS newdate FROM tablename;

    where:

    • %M is the month name (January..December)
    • %e is the day of the month, numeric 0..31
    • %Y is the year