format datetime mysql code example
Example 1: mysql format date
DATE_FORMAT(date, format)
SELECT DATE_FORMAT(dateField, '%m/%d/%Y') FROM TableName;
Example 2: mysql pretty date format
DATE_FORMAT(NOW(),'%M %e, %Y @ %h:%i %p')
Example 3: mysql date format
SELECT STR_TO_DATE(my_date_col,'%d.%m.%Y') AS my_strdate FROM my_table;
SELECT DATE_FORMAT(STR_TO_DATE(my_date_col,'%d.%m.%Y'), '%Y-%m-%d') AS my_strdate
FROM my_table;
Example 4: format time mysql
"45": "09:00",
"24": "10:00",
"42": "11:00",
"48": "12:00",
$this->virtualFields['time'] = "DATE_FORMAT(MemberBookFacility.time, '%H:%i')";
return $this->find('list', array(
'conditions' => $conditions,
'fields' => array(
'MemberBookFacility.id',
'time',
),
'order' => array(
'MemberBookFacility.time ASC',
),
));