date in sql

[varsha]

hi all,
i have date stored in database in yyyy-mm-dd format..
bt while displaying on page i wanna display in d-m-yyyy fomat...
how do i do tht?
like eg.....
event_date=2007/01/20-------saved in database..
bt i want to display like 20th Jan 2007....
similiarly for time...in database it is saved as 02:30:00....
bt i want to display it as 02:30 AM....


hw do i do this?


its urgent

regrds
varsha :P

[taith]

heres a quick way of formatting the date... i'm sure you can find a similar way to do the time also :-)
[code]<?
function switch_month($m){
while($m{0}=="0") $m = substr($m,1);
$m = strtolower($m);
if($m==1) $M="january";
if($m==2) $M="february";
if($m==3) $M="march";
if($m==4) $M="april";
if($m==5) $M="may";
if($m==6) $M="june";
if($m==7) $M="july";
if($m==8) $M="august";
if($m==9) $M="september";
if($m==10) $M="october";
if($m==11) $M="november";
if($m==12) $M="december";
if($m==january) $M="1";
if($m==february) $M="2";
if($m==march) $M="3";
if($m==april) $M="4";
if($m==may) $M="5";
if($m==june) $M="6";
if($m==july) $M="7";
if($m==august) $M="8";
if($m==september) $M="9";
if($m==october) $M="10";
if($m==november) $M="11";
if($m==december) $M="12";
return $M;
}
$event_date='2007/01/20';
$date=explode('/',$event_date);
$date=$date[2].' '.switch_month($date[1]).' '.$date[0];
?>[/code]

[paul2463]

you can also look <a href="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html"> Here at the MYSql Date </a> functions

[code]
<?php
$query = "SELECT DATE_FORMAT(event_date, '%e %b %Y') FROM table WHERE whatever";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
echo $row['event_date']; //displays 3 Jan 2007
?>
[/code]

[matto]

If you are using mysql you could use the date_format function in your sql statement

example:
[code]
select date_format(date_field, '%d-%m-%Y') as date from....
[/code]

;)

looks like paul2463 got there first....