CODEWITHSUNDEEP
phpmysqlsqldatetime
Miss Rosy
Miss Rosy

Reputation: 1053

How to separate 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 a single MySQL query?

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

Upvotes: 101

Views: 181613

Answers (6)

Sohan Arafat
Sohan Arafat

Reputation: 93

An easy way would be:

For date:

SELECT DATE(datetime_column) as mydate

For time:

SELECT TIME(datetime_column) as mytime

Upvotes: -2

Frank Thomas
Frank Thomas

Reputation: 2514

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;

Upvotes: 66

Thushara Buddhika
Thushara Buddhika

Reputation: 1

Simply,
SELECT TIME(column_name), DATE(column_name)

Upvotes: 0

Swapnil
Swapnil

Reputation: 37

For only date use
date("Y-m-d");

and for only time use
date("H:i:s");

Upvotes: -3

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

Try:

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

Upvotes: 18

John Woo
John Woo

Reputation: 263693

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

Upvotes: 156

Related Questions