Issue

I am storing a DATETIME field in a table. Each value looks something like this:

2012-09-09 06:57:12

I am using this syntax:

date("Y-m-d H:i:s");

Now my question is, while fetching the data, how can get both date and time separately, using a single MySQL query?

Date like "2012-09-09" and time like "06:57:12".

Solution

You can achieve that using DATE_FORMAT() (click the link for more other formats)

SELECT DATE_FORMAT(colName, '%Y-%m-%d') DATEONLY, 
       DATE_FORMAT(colName,'%H:%i:%s') TIMEONLY

SQLFiddle Demo

Answered By – John Woo

Answer Checked By – Candace Johnson (BugsFixing Volunteer)