Format date values in mysql query

Posted: November 12, 2007 in Mysql Stuff, Mysql Tips
Tags: , ,

By default Mysql store and returen ‘date’ data type values in “YYYY/MM/DD” format. So if we want to display date in different format then we have to format date values as per our requirement in scripting language.

Like in PHP if i want to display Date of Birth of student in “DD/MM/YYYY” format then i have to do something like this –

<?php
$sql = "select dob from student where Id=1";
$rst = mysql_query($sql);if($rst)
{
    $row = mysql_fetch_array($rst);
    $dob = $row['dob'];
    echo "Date of birth - "substr($date,0,4)."/".substr($date,5,2)."/".substr($date,8,2);
}?>

we can format date in mysql query itself using date_format function eg. As per our last example, to get date of birth in “DD/MM/YYYY” format we can use this query –
<?php
$sql ="select date_format(dob,'%d/%m/%Y') dob from student where Id=1";
$rst = mysql_query($sql);if($rst)
{
    $row = mysql_fetch_array($rst);
    echo "Date of birth - "$row['dob'];
}?>

Here are the detailed information about mysql date_format function.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s