Format timestamp from MYSQL query

Question

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

2013-03-27 18:32:45

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

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

I would like to handle this at the sql level rather than 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

Solution 1

You will 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 docs will show what specifiers you will use to get the format that you want (See SQL Demo).

If you want to keep seconds, then you will use:

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

Solution 2

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