Balakumar B Balakumar B - 5 months ago 10
SQL Question

How to ordinal form date using DATE_FORMAT()?

My Mysql query

select DATE_FORMAT(max(lastmodified), '%d %M %y , %r') from client_log


and my actuall output is

enter image description here

i want result 15th june 16, 02:17:09PM, i have to apply this ordinal concept in php logic but here i don't know how to perform in date_format() function or is there any function available for mysql in timestamp filed?

Answer

The issue is %d and it should be %D

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

  select DATE_FORMAT(NOW(), '%D %M %y , %r') ;
    +-------------------------------------+
    | DATE_FORMAT(NOW(), '%D %M %y , %r') |
    +-------------------------------------+
    | 27th June 16 , 02:57:00 PM          |
    +-------------------------------------+
    1 row in set (0.04 sec)
Comments