How To Print MySQL Dates In mm/dd/yyyy And dd/mm/yyyy Formats
Submitted by Sudheer on Sun, 07/06/2008 - 14:10.
When choosing the MySQL field type to store dates, date is the preferred type. MySQL stores dates in the format yyyy-mm-dd which is seldom used to print dates on web pages. As a PHP developer you will eventually come across a need to convert the MySQL date format into dd-mm-yyyy or mm-dd-yyyy or another format. This post tells you how you can do that in your PHP programs.
Let us create a hypothetical situation to demonstrate the program.
Create the MySQL table.
CREATE TABLE dates (
name varchar(30),
birthday date
) Insert sample data with dates.
INSERT INTO dates (
name ,
birthday
)
VALUES (
'Natasha', '1960-05-10'
), (
'Joshua', '1943-04-03'
), (
'Alex', '1971-02-09'
), (
'Laura', '1932-11-13'
), (
'Tina', '1991-08-12'
);Let us write a PHP program to connect to the MySQL database, select Laura's birthday and print it in various formats.
<?php
mysql_connect("localhost", "dbuser", "password");
mysql_select_db("db");
$query = "SELECT birthday FROM dates WHERE name='Laura'";
$result = mysql_query($query);
$row = mysql_fetch_object($result);
$luara_birthday = $row->birthday;
?>At this point in our code, Laura's birthay is stored in the variable $luara_birthday. We will perform date format conversion operations on this variable.
Birthday as is.
<?php
echo "<br />Luara's birthday is on " . $luara_birthday;
?>Create a PHP DateTime object.
<?php
$dateTime = new DateTime($luara_birthday);
?>PHP 5 onwards provides the date_format() function which is very useful to convert date formats. PHP's date() function accepts various characters to output date string. The date formats accepted date() can also be used with date_format().
<?php
//Convert birthday to dd-mm-yyyy
$luara_birthday = date_format($dateTime, "d-m-Y");
?>The date() function understands the string "d-m-Y". Therefore date_format() also understands "d-m-Y".
<?php
echo "<br />After converting the date format to dd-mm-yyyy Luara's birthday looks like " . $luara_birthday;
?>Similarly we use the string "m-d-Y" to convert the date format to mm-dd-yyyy.
<?php
//Convert birthday to mm-dd-yyyy
$luara_birthday = date_format($dateTime, "m-d-Y");
echo "<br />After converting the date format to mm-dd-yyyy Luara's birthday looks like " . $luara_birthday;
?>I often use these functions to convert date formats.
<?php
function convert_mysql_date_to_ddmmyyyy($date) {
$dateTime = new DateTime($date);
$date_converted=date_format ( $dateTime, "d-m-Y");
return $date_ind;
}
function convert_mysql_date_to_mmddyyyy($date) {
$dateTime = new DateTime($date);
$date_converted=date_format ( $dateTime, "m-d-Y");
return $date_ind;
}
?>Bookmark/Search this post with:
Post new comment