CODEWITHSUNDEEP
mysqlsql
Amit Yadav
Amit Yadav

Reputation: 1861

How to change the date format in sql queries

I have date and time format as 

2011-12-31 05:12:23

And i need to change it as follows: 

Monday @ 2:23AM 21/11/2011

I am using this sql query:

SELECT * FROM cms_content order by ID DESC;

where i have the fields as follows in my table named cms_content:

id, idname, author, date, title, body

So how to do this so that i can get the desired result?

Upvotes: 0

Views: 7377

Answers (1)

Nonym
Nonym

Reputation: 6299

Try this:

SELECT DATE_FORMAT(`date`,  '%W @ %l:%i%p %d/%m/%Y') FROM cms_content

Read further here:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

[edit : additional]

I noticed a difference between your sample and expected data output so here's a literal answer to your date format inquiry:

SELECT DATE_FORMAT( DATE_ADD('2011-12-31 05:12:23', INTERVAL '-40 2:49' DAY_MINUTE ) ,  '%W @ %l:%i%p %d/%m/%Y' )

Upvotes: 1

Related Questions