trim output of datetime field

[ldoozer]

Hi all, can anyone tell me the best way to only display the date in the output of a datetime mysql field.
So I have 2006-11-28 20:54:46 stored but only want to display 2006-11-28.

[The Little Guy]

[CODE]<?php

$date = '2006-11-28 20:54:46';

list($year,$time) = explode(" ",$date);

echo $year;
?>[/CODE]

[roopurt18]

Alternatively you can tell MySQL to return only that part of the field and avoid modifying it in PHP altogether.

[ldoozer]

how would i do that? (embarrased)

[roopurt18]

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

DATE([i]expr[/i])
SELECT DATE('2003-12-31 01:02:03')

So if you have a table [i]the_table[/i] and the DATETIME column is named [i]the_date[/i]:
SELECT *, DATE(the_date) AS the_date_disp FROM the_table WHERE 1

Also, if you want to format the date like you can with PHP's date function, you'll want to look up DATE_FORMAT in MySQL.
SELECT * DATE_FORMAT(the_date, '%a. %b. %D, %Y') AS the_date_disp FROM the_table WHERE 1

Hope that helps.

[ldoozer]

Thanks very much for all the help.  :)