Format timestamp from MYSQL query

Question

I have a mysql query that has a CreationDate column in "timestamp" format. Example:

2013-03-27 18:32:45 

I would like the sql query to format it this way (12hr format with seconds):

 Friday 3/28/2013 12:52:34 PM 

I would like to handle this at sql level, not php or .js etc.

Query:

 SELECT a.ID, a.CreationDate, a.Content, a.Type, u.Nickname FROM Announcements a INNER JOIN Accounts u ON a.FromAccountID = u.AccountID WHERE a.Status = '1' AND u.Status = '1' ORDER BY a.ID DESC 

Answer 1

You want to use DATE_FORMAT :

 SELECT a.ID, date_format(a.CreationDate, '%W %m/%d/%Y %l:%i %p') CreationDate, a.Content, a.Type, u.Nickname FROM Announcements a INNER JOIN Accounts u ON a.FromAccountID = u.AccountID WHERE a.Status = '1' AND u.Status = '1' ORDER BY a.ID DESC 

The MySQL documentation will show which qualifiers you will use to get the format you want (see SQL Demo ).

If you want to save seconds, you will use:

 date_format(a.CreationDate, '%W %m/%d/%Y %r') 

Answer 2

DATE_FORMAT(a.CreationDate,'%W %c/%e/%Y %h:%i %p')