mysql:show date from datetime

Question

hello all... i have a field inside table inspection,it is inspection_datetime. the type of field is datetime, so that make the data like yy/mm/dd hh:mm:ss. my question is how do i do if i want to show date only from the data?

Answer 1

Or you can use Date_Format if you want MySQL to do it:

SELECT DATE_FORMAT(inspection_datetime, "%Y/%m/%d")

Answer 2

If you have access to PHP 5.3.0 or greater, the DateTime class DateTime::format function makes formatting dates and times easy:

$dt = new DateTime($db_datetime);

echo $dt->format('Ymd');
// 20101231

echo $dt->format('Y-m-d');
// 2010-12-31

echo $dt->format('Y-M-d');
//2010-Dec-31

Answer 3

There are a few solutions, including formatting the data in SQL or in PHP as given in other answers.

Another option is to return just the date portion from the datetime column:

SELECT DATE(inspection_datetime) FROM inspection