CODEWITHSUNDEEP
mysqlsqldate
user1157800
user1157800

Reputation: 91

Format timestamp from MYSQL query

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

Upvotes: 6

Views: 22204

Answers (2)

Taryn
Taryn

Reputation: 247690

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

Upvotes: 8

brbcoding
brbcoding

Reputation: 13586

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

Upvotes: 1

Related Questions