Converting a date in MySQL from string field

Question

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.)

OMG Ponies · Accepted Answer · 2009-12-07 17:34:09Z

129

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.

answered Dec 7, 2009 at 17:34

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aditya P Bhatt Over a year ago

its helpful and helped me a lot for a quick solution

рüффп Over a year ago

Good to mention DATE_FORMAT if the column is already in Date format.

nos · Accepted Answer · 2009-12-07 17:29:42Z

17

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)

answered Dec 7, 2009 at 17:29

nos

231k60 gold badges436 silver badges516 bronze badges

Comments

monksy · Accepted Answer · 2009-12-07 17:29:26Z

4

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

answered Dec 7, 2009 at 17:29

monksy

14.2k19 gold badges78 silver badges128 bronze badges

Comments

h4rp0 · Accepted Answer · 2009-12-07 17:31:23Z

0

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

answered Dec 7, 2009 at 17:31

h4rp0

1211 silver badge6 bronze badges

Collectives™ on Stack Overflow

Converting a date in MySQL from string field

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related