DATE_FORMAT: Not returning expected format

[eluser]ShawnMA[/eluser]
I've searched all over and can't seem to find a solution. User entered dates are converted from a string (05/15/2011 2:37 pm) into MySQL datetime (ex: 2011-05-30 14:37:00), and when selecting them back out of the database I'm attempting to reformat them as (05/15/2011 2:37 pm), but the conversion isn't working properly.

When performing the following:

Code:

$this->db->select("DATE_FORMAT(event_datetime, '%W %M %d, %Y @ %h:%i %A') AS event_dt", FALSE);

I get this result:
"event_dt":"Saturday May 15, 2011 @ 2:37 A"

Instead of the expected: Saturday May 15, 2011 @ 2:37 PM

For some reason, which I've struggled to find the answer to, %W = Day of the week vs. what PHP says should be "ISO-8601 week number of year, weeks starting on Monday" and %A = A, which PHP says should be "Uppercase Ante meridiem and Post meridiem AM or PM"

Any thoughts on what I'm doing wrong? Or why PHP says is should come out one way and it comes out another?

Thanks!

[eluser]skunkbad[/eluser]
I usually resort to using php to do my formatting and store the dates as int(10). Even when storing as date or datetime, I end up converting that to another format with php. I actually wish CI handled this better. Maybe somebody can teach us some tricks.

[eluser]plain jane[/eluser]
Please try this

Code:

$this->db->select("DATE_FORMAT(event_datetime, '%W %M %d, %Y @ %h:%i %p') AS event_dt", FALSE);

Thanks,
ci user

[eluser]danmontgomery[/eluser]
http://dev.mysql.com/doc/refman/5.5/en/d...ate-format

%p AM or PM

[eluser]ShawnMA[/eluser]
Thanks!

[eluser]Unknown[/eluser]
Thanks! helped me too.