Formating mysql date

[discostudio]

My dates are stored in my database are in this format:

2004-10-04

and my datetime's in my database are

2004-10-04 18:22:00

how do I format it, one extracted from the database, to display the date in British format like:

04/10/2004

and the datetime like this:

18:24 04/10/2004

I also need to be able to format the date to look like this:

4th October, 2004
and
Monday 4th October, 2004

Can anyone help?

[Shrike]

Take a look at the [MAN]date[/MAN] function.

[discostudio]

Good answer, but once I have extracted the date in question from the database, I have it stored in a variable:

$dateinquestion=$r[DatePaid];

So how would I reformat 04-10-2004 to:

04/10/2004

date($dateinquestion, "reformat???", $newdate);

with the date function, it doesn't tell me how to do that, just how to create the date format I want from scratch, not from a variable.

[discostudio]

<table width="100%"  border="1" cellspacing="0" cellpadding="0">
  <tr>
    <td>Date</td>
    <td>Transaction ID</td>
    <td>User</td>
    <td>Amount</td>
    <td>AVSCV2</td>
  </tr> <?
  $orders = mysql_query("SELECT * FROM trolley WHERE active = '0' ORDER BY DatePaid DESC LIMIT $start,$numentries");
  /*WHERE active = '0' OR TxAuthNo > 0 */
  while($row = mysql_fetch_array($orders)) { 
  $query = mysql_query("SELECT * FROM customers WHERE id ='$row[Owner]'");
  $cust = mysql_fetch_array($query);

  ?>
  <tr>
    <td><a href="view_contents.php?trolleyid=<?php echo $row[TrolleyId]; ?>"><?php echo $row[DatePaid]; ?></a></td>
    <td><?php echo $row[VendorTxCode]; ?></td>
    <td><a href="view_contents.php?trolleyid=<?php echo $row[TrolleyId]; ?>"><?php echo $cust[name1]." ".$cust[name2]; ?></a></td>
    <td>&pound; <?php echo number_format($row[Total], 2); ?></td>
    <td>&nbsp;<?php echo $row[AVSCV2]; ?></td>
  </tr>
  <?php }  ?>
</table>

I want this date to be displayed in this format, I have looked at the date() function and cannot wrok it out:

2004-10-04 --->>> 04/10/2004

😕

[yelvington]

No.

Use the date formatting capability of mysql to reformat the date in the sql query.

Look up DATE_FORMAT in the MySQL manual:

http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

[Shrike]

Well there is something to be said for keeping your code non-database-specific. So the PHP way:

$r['DatePaid'] = date("d/m/Y", strtotime($r['DatePaid']));

[bodzan]

It is not the nicest solution but it will work (if the dates are allways stored like this in database 0000-00-00):

$date_from_db;

$year = substr($date_from_db,0,4);

$month = substr($date_from_db,5,7);

$day = substr($date_from_db,8,10);

echo $year."/".$month."/".$day;//this would echo your date in wanted format

Similar is for the time part...