101

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

informatik01
  • 16,038
  • 10
  • 74
  • 104
Miss Rosy
  • 1,053
  • 2
  • 9
  • 9
  • probably you're looking for `DATE_FORMAT` and not `DATE` and `TIME` because it will give you default date if you are looking for time and default time if you are looking for date, see my answer below. – John Woo Sep 09 '12 at 07:23

6 Answers6

154

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

John Woo
  • 258,903
  • 69
  • 498
  • 492
62

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;
informatik01
  • 16,038
  • 10
  • 74
  • 104
Frank Thomas
  • 2,434
  • 1
  • 17
  • 28
  • 1
    It's nice to know that comparisons on this work as expected, e.g. `SELECT * FROM sales WHERE TIME(sold_at_utc) >= '12:00:00';` - to get sales recorded after noon, regardless of their date. – Jan Żankowski Jan 15 '21 at 19:23
18

Try:

SELECT DATE(`date_time_field`) AS date_part, TIME(`date_time_field`) AS time_part FROM `your_table`
Sudhir Bastakoti
  • 99,167
  • 15
  • 158
  • 162
  • this will not get the `DATE` and `TIME` only as what the user is asking. It will give the whole date and time with its defaults. [SELL THIS LINK](http://sqlfiddle.com/#!2/d41d8/1845) – John Woo Sep 09 '12 at 07:20
  • 1
    it will generate `September, 09 2012 00:00:00-0400` for *DATE* and `January, 01 1970 03:20:38-0500` for *TIME* – John Woo Sep 09 '12 at 07:21
  • 4
    well, as per query it gives 2012-09-09 as date_part and 06:57:12 as time part in mysql – Sudhir Bastakoti Sep 09 '12 at 07:23
  • @Sudhir..Thanks a lot for replying.This is also correct but..using `DATE_FORMAT()` make you more flexible. – Miss Rosy Sep 09 '12 at 07:33
  • 1
    @MissRosy welcome, and yes you should be using a solution that suits the most and is more flexible.. :) – Sudhir Bastakoti Sep 09 '12 at 07:35
0

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

Thushara Buddhika
  • 1
-2

An easy way would be:

For date:

SELECT DATE(datetime_column) as mydate

For time:

SELECT TIME(datetime_column) as mytime
mejem
  • 53
  • 7
Sohan Arafat
  • 93
  • 2
  • 16
-3

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

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

Thirumalai murugan
  • 5,698
  • 8
  • 32
  • 54
Swapnil
  • 37