Search code examples
mysqlsqldate-conversion

convert irregular date in mysql

I am working with a mysql database that somebody else created. I have a table with two different date fields one is called eventDate and is in the format YYYY-MM-DD and the other is called creationDate and is in the format M/D/YYYY H:MM:SS AM/PM

What I want to do is query the table and return results where the eventDate is the same as the creationDate but how do I convert the creationDate into YYYY-MM-DD format?

Solution

  • Try STR_TO_DATE() and a format matching the one used by your creationDate strings:

    SELECT
…
WHERE DATE(STR_TO_DATE(creationDate, '%c/%e/%Y %r')) = STR_TO_DATE(eventDate, '%Y-%m-%d')

    Perhaps MySQL would be able to convert YYYY-MM-DD strings to dates implicitly, so the second STR_TO_DATE() call might be unnecessary.