Change mysql date type into another date format with mysql’s date_format function

Posted by Shek on April 12, 2011 under MySQL | Be the First to Comment | Total View: 10,633 views

MySQL’s date field type default is in yyyy-mm-dd format, and it is not practical in some cases when we want to use another formating representation such as dd/mm/yyyy. Of course we could use PHP to convert it but the best way I find is to convert it through SQL before any php operation, here is an example which I which like to show you:

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:

1
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:

1
DATE_FORMAT(birthday,'%d/%m/%Y');

And with select query, we have:

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

Not what you want? Try a Search

Add A Comment

*