MySQL DATE_FORMAT ()

Question

I am trying to format a date using MySQL DATE_FORMAT. My request

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

added_date is the date field in the profile table, when I run this query, it brings Null for AddDate, any sentence.

Thanks.

Answer 1

Based on your comments, you're 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 

Output Example:

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

Here is the SQLFiddle demo

Answer 2

If you store 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 

Answer 3

Try something like this using STR_TO_DATE () : -

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

Answer 4

find edited query

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

Link to link