How to part DATE and TIME from DATETIME in MySQL

Question

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 the single MySQL query?

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

Answer 1

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

Answer 2

per the mysql documentation, the DATE() function will pull the date part of a datetime feild, and TIME() for the time portion. so I would try:

select DATE(dateTimeField) as Date, TIME(dateTimeField) as Time, col2, col3, FROM Table1 ...