Converting a date in MySQL from string field

I’m using a system where the dates are stored as strings in the format dd/mm/yyyy. Is it possible to convert this to yyyy-mm-dd in a SELECT query (so that I can use DATE_FORMAT on it)? Does MySQL have a date parsing function?

Currently the only method I can think of is to concatenate a bunch of substrings, but hopefully there’s a simpler solution.

(Unfortunately I can’t convert the field to a true date field since it’s a meta-table: the same column contains values for different fields that are just strings.)

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This:

STR_TO_DATE(t.datestring, '%d/%m/%Y')

…will convert the string into a datetime datatype. To be sure that it comes out in the format you desire, use DATE_FORMAT:

DATE_FORMAT(STR_TO_DATE(t.datestring, '%d/%m/%Y'), '%Y-%m-%d')

If you can’t change the datatype on the original column, I suggest creating a view that uses the STR_TO_DATE call to convert the string to a DateTime data type.

Method 2

Yes, there’s str_to_date

mysql> select str_to_date("03/02/2009","%d/%m/%Y");
+--------------------------------------+
| str_to_date("03/02/2009","%d/%m/%Y") |
+--------------------------------------+
| 2009-02-03                           |
+--------------------------------------+
1 row in set (0.00 sec)

Method 3

STR_TO_DATE allows you to do this, and it has a format argument.

Method 4

SELECT STR_TO_DATE(dateString, '%d/%m/%y') FROM yourTable...

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating