CODEWITHSUNDEEP
phpmysql
SundayaMizo
SundayaMizo

Reputation: 43

how to format date from mysql database inside SQL query

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 !

Upvotes: 1

Views: 79

Answers (2)

Shailesh Chauhan
Shailesh Chauhan

Reputation: 673

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.

http://www.w3schools.com/sql/func_date_format.asp

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

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')

Upvotes: 2

Related Questions