I have a field called: news_date in a my sql db... it is a "datetime" thing, and it stores the date in this format 0000-00-00 00:00:00 .. how can I display the date in other formats? eg. the day and the month ?

You need to use DAYOFMONTH() and MONTH(). See http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html for more functions.

but what code do I use?

$news_date(DAYOFMONTH())

???

I made two php functions for inserting/extracting dates
in a nice format.
I didn't care to use sql's built-in functions.

To insert a date in format of:
2006-03-12 06:23:02

function insertDate()
{
$timestamp = time()+date("Z");
$time = gmdate("Y-m-d H:i:s",$timestamp);
return $time;
}

and to call above use something like:

$date = insertDate();


For display from database I do:

function niceDate($daterow)
{
$day = substr($daterow, 8, 2);
$month = substr($daterow, 5, 2);
$year = substr($daterow, 0, 4);
$date = "$month-$day-$year";
return $date;
}

which displays as 03-12-2006
and to use in querys would be along the lines of:

$date = niceDate($row['date']);


Of course you can easily change it to display/insert however you choose.

This is a MySQL function, so you need to incorporate it into your query. Alternatively you would need to parse the string value you get from MySQL (maybe strtotime() (http://www.php.net/manual/en/function.strtotime.php) works as well, you would need to try it).

What zoid said is correct.

It's easiest to format your date/time field within the query. This is an example of a unix timestamp being formatted in the query.


$query = "SELECT FROM_UNIXTIME(topic_time,'%M %D, %y %H:%i:%S') "
$query .= "as topic_time, topic_title, topic_id FROM phpbb_topics WHERE (((forum_id)=13))";


(chopped query to keep forum width)