How to part DATE and TIME from DATETIME in MySQL

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.

Solution 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

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 ...

Try:

SELECT DATE(`date_time_field`) AS date_part, TIME(`date_time_field`) AS time_part FROM `your_table`