Change mysql date type into another date format with mysql’s date_format function
We have a field called “birthday” in MySQL table which stores all the birthday of people, we would like to have dd/mm/yyyy format.
In MySQL, the function we are using is date_format and the syntax is:
DATE_FORMAT(date or date field,'date format')
and in order to have format dd/mm/yyyy, we have to use the Specifier below:
%d – for day 00-31
%m – for month 00-12
%Y – for year in 4 digits
and which mean we will use %d/%m/%Y
With the DATE_FORMAT() function:
And with select query, we have:
select DATE_FORMAT(birthday,'%d/%m/%Y') as birthday from table
However with PHP, the single quote for the date format should use \’ instead in order for it to work:
1 2 3
<?php $query = 'select DATE_FORMAT(birthday,\'%d/%m/%Y\') as birthday from table'; ?>
Now we have format dd/mm/yyyy instead of the default yyyy-mm-dd.