Quick Question About the DATE Column Type.

I have a DATE column in my database. When I run a SELECT query using MySQL or PHP, the date is displayed like this: yyyy-mm-dd.

How can I get it to display like the following?

21 February, 2008

Is there a different MySQL column type that I must use? Or, must I continue to use the DATE column type but change the way the date is displayed using PHP? If so, please show me how, and I'll add to your reputation!

 

You can use MySQL's DATE_FORMAT() function show this type of output. e.g.

SELECT DATE_FORMAT(date_column, '%d %M, %Y') FROM table

Code (markup):

 

Hi, Mwasif!

Thanks for the help, man! Reputation points added!

Do you know how I might be able to incorporate your suggestion into the following code? Note that my DATE column is called offer_expiration_date (rendered in bold).



<?php

$con = mysql_connect("localhost","root");

if (!$con)

{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("ctyi", $con);

$result = mysql_query("SELECT

Coupon_Table.offer_anchor_text,
Coupon_Table.offer_url,
Coupon_Table.offer_expiration_date,
Coupon_Table.coupon_code,

Advertiser_Table.advertiser_logo,
Advertiser_Table.advertiser_name,
Advertiser_Table.advertiser_url,

Category_Table.category_name,
Category_Table.category_url,

Offer_Instructions_Table.block_anchor_text,
Offer_Instructions_Table.offer_instructions

FROM Coupon_Table, Advertiser_Table, Category_Table, Offer_Instructions_Table
WHERE Coupon_Table.advertiser_id=Advertiser_Table.advertiser_id
AND Coupon_Table.category_id=Category_Table.category_id
AND Coupon_Table.offer_instructions_id=Offer_Instructions_Table.offer_instructions_id");

while($row = mysql_fetch_array($result))

{
echo "<table border\"0\">";
echo "<tr>";
echo "<td><img src=\"" . $row['advertiser_logo'] . "\" alt=\"\" /></td>";
echo "<td><a href=\"" . $row['offer_url'] . "\">" . $row['offer_anchor_text'] . "</a><br />";
echo (empty($row['coupon_code']) ? $row['offer_instructions'] : $row['coupon_code']);
echo "<br /> Offer expires: " . $row['offer_expiration_date'] . "<br />";
echo "About <a href=\"" . $row['advertiser_url'] . "\">" . $row['advertiser_name'] . "</a><br />";
echo "Category: <a href=\"" . $row['category_url'] . "\">" . $row['category_name'] . "</a></td>";
echo "<td><a href=\"" . $row['offer_url'] . "\">" . $row['block_anchor_text'] . "</a></td>";
echo "</tr>";
echo "</table>";
}

mysql_close($con);

?>

 

<?php

$con = mysql_connect("localhost","root");

if (!$con)

{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("ctyi", $con);

$result = mysql_query("SELECT

Coupon_Table.offer_anchor_text,
Coupon_Table.offer_url,
DATE_FORMAT(Coupon_Table.offer_expiration_date, '%d %M, %Y') AS formated_date,
Coupon_Table.coupon_code,

Advertiser_Table.advertiser_logo,
Advertiser_Table.advertiser_name,
Advertiser_Table.advertiser_url,

Category_Table.category_name,
Category_Table.category_url,

Offer_Instructions_Table.block_anchor_text,
Offer_Instructions_Table.offer_instructions

FROM Coupon_Table, Advertiser_Table, Category_Table, Offer_Instructions_Table
WHERE Coupon_Table.advertiser_id=Advertiser_Table.advertiser_id
AND Coupon_Table.category_id=Category_Table.category_id
AND Coupon_Table.offer_instructions_id=Offer_Instructions_Table.offer_instructions_id");

while($row = mysql_fetch_array($result))

{
echo "<table border=\"0\">";
echo "<tr>";
echo "<td><img src=\"" . $row['advertiser_logo'] . "\" alt=\"\" /></td>";
echo "<td><a href=\"" . $row['offer_url'] . "\">" . $row['offer_anchor_text'] . "</a><br />";
echo (empty($row['coupon_code']) ? $row['offer_instructions'] : $row['coupon_code']);
echo "<br /> Offer expires: " . $row['formated_date'] . "<br />";
echo "About <a href=\"" . $row['advertiser_url'] . "\">" . $row['advertiser_name'] . "</a><br />";
echo "Category: <a href=\"" . $row['category_url'] . "\">" . $row['category_name'] . "</a></td>";
echo "<td><a href=\"" . $row['offer_url'] . "\">" . $row['block_anchor_text'] . "</a></td>";
echo "</tr>";
echo "</table>";
}

mysql_close($con);

?>

 

Thanks, Mwasif! Works great!

 

You welcome