datetime formatting, argh!

[undershot]

Hi, I'm trying to get a website running where I can do articles on bands, I have 3 tables in my SQL database, articles, writers, and bands, writers and bands has details about well... the writers and bands, and articles has the articles in it funnily enough, but includes the id of the writer or band in question. The whole thing connects together wonderfully.

EXCEPT...

When I try to format the date that has come out of my database in the datetime format, it just can't be done for some unknown reason, I've tried everything, even using date_format in my query but it just ain't having it. I always seem to get the date in its full datetime format or when I do manage to format it, it gives me the date 01.01.1970 instead, no matter what the date in the database is.

I have included my code below, any help would be much appreciated.

Oh, and this webpage can be found here... http://www.undershot.co.uk/index.php?page=Articles

Ryan x

<?php
mysql_select_db($database_undershotcouk, $undershotcouk);
$query_Articles = "SELECT articles.article_id, articles.article_title, articles.article_date, writers.writer_name, bands.band_name FROM articles LEFT JOIN writers ON articles.article_writer = writers.writer_id LEFT JOIN bands ON articles.article_band = bands.band_id ORDER BY articles.article_date DESC";
$Articles = mysql_query($query_Articles, $undershotcouk) or die(mysql_error());
$row_Articles = mysql_fetch_assoc($Articles);
$totalRows_Articles = mysql_num_rows($Articles);




**do { ?>
******<?php $id = $row_Articles['article_id']; ?>
******<?php $band = $row_Articles['band_name']; ?>
******<?php $title = $row_Articles['article_title']; ?>
******<?php $writer = $row_Articles['writer_name']; ?>
******<?php $date =**$row_Articles['article_date']; ?>
****
****<p><a href="index.php?page=Articles&article=<?php echo $id; ?>">
****<b><?php echo $band; ?></b> - <?php echo $title; ?></a><br>
****<span class="indent"><?php echo $writer; ?> - <?php echo $date; ?></span></p>
**
**<? } while ($row_Articles = mysql_fetch_assoc($Articles));



mysql_free_result($Articles);
?>

[devinemke]

i assume that articles.article_date is a DATETIME type field? your code as posted is not doing any formatting, so the date will be displayed in the default mySQL datetime format (YYYY-MM-DD HH:MM:SS). what formatting do you want? the DATE_FORMAT function can format it any way you wish.

[undershot]

I'm looking for it in d/m/Y, and possibly add times at a later date, but I imagine 1ce I've got the general coding, I'll be able to add the time later on, for the mean time though, just d/m/Y.

I removed all date(), date_format........ tags before I posted this topic as none of them were working.

Also, yes, articles.article_date is a datetime field.

Ryan x

[devinemke]

SELECT articles.article_id, articles.article_title, DATE_FORMAT(articles.article_date, '%d/%m/%y') AS date, writers.writer_name, bands.band_name FROM articles LEFT JOIN writers ON articles.article_writer = writers.writer_id LEFT JOIN bands ON articles.article_band = bands.band_id ORDER BY articles.article_date DESC

[undershot]

Cheers mate, it was the ' AS date' bit I kept missing out.

Musch appreciated, Ryan x