1

I am working on a database about global COVID-19 vaccinations. In this database, there's a column named date with text data type and the date is in the format mm/dd/yyyy. I have tried to use str_to_date(), date_format() or date() to convert the column to date data type in the SELECT query but it only works for the format yyyy/mm/dd. May I ask how to convert this date column, with this format mm/dd/yyyy to date for SELECT query use, without the need to ALTER TABLE?

adrenaline245
  • 33
  • 6

1 Answers1

1

The built-in MySQL function you are looking for is str_to_date(). You can pass this function a string containing a date, in a specified format, and it will return a datetime value. See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format for format specifiers.

example:

select str_to_date('08/28/2021', '%m/%d/%Y');

returns:

2021-08-28
mti2935
  • 11,465
  • 3
  • 29
  • 33