how to format date from mysql database inside SQL query

Question

I have a column named date_posted in MySQL database in the form of 2014-11-22 12:45:34 . I need to get only the date, month and year. So, I do

SELECT DATE(date_posted) as date_p FROM tablename

So, I get a format as 2014-11-22. How can I get it in the form 22 Nov, 2014. And can it still be used for sorting the results.

Thank you :D !

Try the DATE_FORMAT function, as found on w3schools.com/sql/func_date_format.asp — Daan Meijer
– Daan Meijer, Commented Jan 17, 2017 at 11:53

Gurwinder Singh · Accepted Answer · 2017-01-17 11:54:09Z

Refering this:

DATE_FORMAT(date_posted, '%d %b, %Y')

And no, this can't be used directly for sorting. You can ,however, parse it to date and then sort later.

order by str_to_date(text_date, '%d %b, %Y')

Shailesh Chauhan · Accepted Answer · 2017-01-17 11:58:57Z

Try SELECT DATE_FORMAT(date_posted, '%d %b, %Y') as your_date FROM table_name

To get more idea about DATE_FORMAT() refer given link.

2 Answers 2