mysql query - format date on output?

Question

In my table, dates are stored like this: 2011-03-03T13:30:00

I'm trying to output dates like this: March 3, 2011 1:30 PM

I'd much rather work it into the query rather than use php to format it, but I'm having some difficulty doing that. Trying various iterations of DATE_FORMAT, but it's not giving me what I want, maybe because of the way it's being stored?

Answer 1

You basically have two different operations you may need to perform when handling dates: date to string and vice versa. The functions you can use are DATE_FORMAT() and STR_TO_DATE(). Full reference can be found in the manual.

Usage example:

SELECT
    DATE_FORMAT(CURRENT_TIMESTAMP, '%d/%m/%Y %H:%i:%s'),
    STR_TO_DATE('31/12/2001 23:55:00', '%d/%m/%Y %H:%i:%s')

If your dates are not real dates but strings, you'll need to convert twice: from string to date and again from date to string:

SELECT
    STR_TO_DATE('2011-03-03T13:30:00', '%Y-%m-%dT%H:%i:%s'),
    DATE_FORMAT(STR_TO_DATE('2011-03-03T13:30:00', '%Y-%m-%dT%H:%i:%s'), '%M %e, %Y %l:%i %p')

Answer 2

Use DATE_FORMAT:

DATE_FORMAT(date, "%M %e, %Y %h:%i %p")

Answer 3

You should really use a DATETIME field for such things (and clean the input on the way in) rather than having to sort this out at the point of output.

Irrespective, you can simply use the DATE_FORMAT function to re-format your field into the format you require, although this might produce some un-expected results on a VARCHAR or CHAR field. (If so, you'll have to use STR_TO_DATE or failing that some various string functions to extract the date bits you require.)

Answer 4

The MySQL date storage format is actually YYYY-MM-DD, but using the str_to_date() and date_format() functions you can accept and generate any date format required.

select DATE_FORMAT(DateTable.MyDate,'%d %b %y')
from DateTable

would return

04 Nov 08