Convert Dates with Mysql

Question

i have a date in this format

May 30 2006 12:00AM

is there a equivalent of strtotime() in mysql that will let me convert this to a mysql date?

Answer 1

I think you are looking for the STR_TO_DATE function. Unfortunately, it is not quite as awesome as PHP's strtotime, so you have to give it a format mask to parse:

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
        -> '2004-04-31'

For your date, I think the mask would be %M %e %Y %l:%i%p, depending on whether or not you are expecting short/long month names and 0-based days. Based on your example it could be either:

mysql> SELECT STR_TO_DATE('May 30 2006 12:00AM', '%M %e %Y %l:%i%p');
+--------------------------------------------------------+
| STR_TO_DATE('May 30 2006 12:00AM', '%M %e %Y %l:%i%p') |
+--------------------------------------------------------+
| 2006-05-30 00:00:00                                    |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Check out the full reference table for the mask options.

Answer 2

You can use STR_TO_DATE (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date).

The format should be something like:

"%b %d %Y %l:%i%p"

Answer 3

you can you the date_format function. This site will help you format the date http://www.mysqlformatdate.com