How to separate DATE and TIME from DATETIME in MySQL?
PhpMysqlSqlDatetimePhp Problem Overview
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".
Php Solutions
Solution 1 - Php
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
Solution 2 - Php
Per the MySQL documentation, the DATE() function will pull the date part of a datetime field, and TIME() for the time portion.
So I would try the following:
SELECT DATE(dateTimeField) as Date,
TIME(dateTimeField) as Time
FROM Table1;
Solution 3 - Php
Try:
SELECT DATE(`date_time_field`) AS date_part, TIME(`date_time_field`) AS time_part FROM `your_table`
Solution 4 - Php
Simply,
SELECT TIME(column_name), DATE(column_name)
Solution 5 - Php
An easy way would be:
For date:
SELECT DATE(column_name) as mydate
For time:
SELECT DATE(column_name) as mytime
Solution 6 - Php
For only date use
date("Y-m-d");
and for only time use
date("H:i:s");