change date format in php / mysql query

I have been attempting to change the date format that is stored in my database so that it displays properly and validates for my RSS feed.

My current code is:

$c = mysqli_connect('localhost', 'user', 'pass', 'database') or die(mysqli_error($c));
$q = mysqli_query($c, 'SELECT * FROM articles ORDER BY id DESC LIMIT 20') or die(mysqli_error($c));

while($r = mysqli_fetch_assoc($q))

	{

		echo '<pubDate>'.$r['DateAct'].'</pubDate>';

	}

Code (markup):

Where "articles" is the table of my database and "DateAct" is the stored date. The date currently appears like so:

2009-04-20 21:19:59

Code (markup):

I was attempting to add something like, "DATE_FORMAT('DateAct', '%W %D %M %Y')" into my query... but failed

 

Don't put quotes around the field name otherwise the database treats it like a string

 

Try doing "SELECT DATE_FORMAT(DateAct, '%W %D %M %Y') FROM" etc...

 

Tried this:

$q = mysqli_query($c, 'SELECT ID, Category_Name, Author, Title, DATE_FORMAT(DateAct, '%W %D %M %Y') FROM articles ORDER BY id DESC LIMIT 20') or die(mysqli_error($c));

Code (markup):

and a bunch of other variations (including REPLACE) but end up with errors such as:

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING

Code (markup):

That line works using DateAct like so:

$q = mysqli_query($c, 'SELECT ID, Category_Name, Author, Title, DateAct FROM articles ORDER BY id DESC LIMIT 20') or die(mysqli_error($c));

Code (markup):

But it produces the date in the following format:

2009-04-20 21:19:59

Code (markup):

Which is exactly how it is stored in that database column.

 

You have to escape: DATE_FORMAT(DateAct, \'%W %D %M %Y\') inside string.
Regards

 

Almost That got rid of the error... but it also got rid of the date in the RSS feed.

 

Than you can get formatted date as $r['FOO']

 

That works... but it is posting the current date instead of what is saved in the database ?

 

convert the date into a unixtime stamp using mktime()

then use the time stamp and date() to output the date/time however you want it.

http://uk2.php.net/manual/en/function.mktime.php
http://uk2.php.net/manual/en/function.date.php

You'll have to split that date string up to do that though using explode or split or something

http://uk2.php.net/manual/en/function.explode.php
http://uk2.php.net/manual/en/function.spliti.php