CODEWITHSUNDEEP
mysqldatetime
TestK
TestK

Reputation: 333

DATE_FORMAT returning null

I'm trying to get a datetime column from my table using a simple SELECT statement, but I want the datetime without seconds.

The datetime column is formatted as 10/21/2013 3:19:33 PM.

My query is SELECT DATE_FORMAT(creationdate, '%m-%d%Y %l:%i %p') AS 'Creation Date' FROM processes;

When I run this query, it returns all NULL values. I've double and triple checked, and the column cells do hold datetime values.

However, when I run SELECT DATE_FORMAT(NOW(), '%m-%d%Y %l:%i %p) AS 'Creation Date' FROM processes; I get the current date with correct formatting.

What could cause this?

Upvotes: 0

Views: 3849

Answers (1)

Nick Rolando
Nick Rolando

Reputation: 26167

creationdate must not be of type datetime if it is formatted 10/21/2013 3:19:33 PM by default.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

Upvotes: 1

Related Questions