How to Separate Date and Time from Datetime in MySQL

Separating date and time from datetime format mysql

Use this query , you will get your desired output :

$sql = "SELECT DATE(datecolumn) as mydate, TIME(datecolumn) as mytime FROM `tablename`";

how to split the datetime column into date and time and compare individually

mysql> select cast('2011-10-24 15:20:10' as date);
+-------------------------------------+
| cast('2011-10-24 15:20:10' as date) |
+-------------------------------------+
| 2011-10-24                          |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('2011-10-24 15:20:10' as time);
+-------------------------------------+
| cast('2011-10-24 15:20:10' as time) |
+-------------------------------------+
| 15:20:10                            |
+-------------------------------------+
1 row in set (0.00 sec)

How to separate DATE and TIME from DATETIME in MySQL?

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

how to separate date and time from same column sql

try this -

select date(Feedback.DateTime) from ctrData2.Feedback;

How to select date from datetime column?

You can use MySQL's DATE() function:

WHERE DATE(datetime) = '2009-10-20'

You could also try this:

WHERE datetime LIKE '2009-10-20%'

See this answer for info on the performance implications of using LIKE.

How to split date and time got from mysql in android

You Can also use StringTokenizer class, it allows you to break string into tokens. You can specify characters that will separate tokens.

Example:

String date = "yyyy-mm-dd hh:mm:ss";
StringTokenizer tk = new StringTokenizer(date);

String date = tk.nextToken();  // <---  yyyy-mm-dd
String time = tk.nextToken();  // <---  hh:mm:ss

---

Related Topics