MySQL DATE_FORMAT()

Question

I'm trying to format date using MySQL DATE_FORMAT. My Query is

SELECT first_name, middle_name, last_name, DATE_FORMAT(added_date,'%d/%m/%Y') AS AddDate
FROM profiles.

added_date is the date field on profile table, when I run that query it brings Null for AddDate, any suggestion.

Thanks.

Answer 1

Based on your comments you are probably looking for this

SELECT first_name, middle_name, last_name, 
       DATE_FORMAT(STR_TO_DATE(added_date, '%d-%m-%Y %h:%i:%s %p'),'%d/%m/%Y') AS AddDate
FROM profiles

Sample output:

| FIRST_NAME | MIDDLE_NAME | LAST_NAME |    ADDDATE |
-----------------------------------------------------
|       Jhon |      (null) |       Doe | 11/08/2013 |

Here is SQLFiddle demo

Answer 2

If you're storing it as an INT you might need to use FROM_UNIXTIME()

SELECT 
  first_name, 
  middle_name, 
  last_name, 
  DATE_FORMAT(FROM_UNIXTIME(added_date),'%d/%m/%Y') AS AddDate
FROM 
  profiles