MySQL Forums
Forum List  »  Newbie

Query syntax
Posted by: Iain MacDonald
Date: February 10, 2007 08:00AM

I'm trying to get a date field to display in the format 01 Jan 2007 rather than mySQLs default format.

My original query looks like :

mysql_select_db($database_connOriental, $connOriental);
$query_rsOrders = "SELECT * FROM Orders INNER JOIN Customers WHERE OrderCustomerID = CustomerID ORDER BY OrderID ASC";
$rsOrders = mysql_query($query_rsOrders, $connOriental) or die(mysql_error());
$row_rsOrders = mysql_fetch_assoc($rsOrders);
$totalRows_rsOrders =

Which works fine, but with the mySQL default format.

So I added a separate query to convert it to the reformatted version :

mysql_select_db($database_connOriental, $connOriental);
$query_DateFormat = "SELECT date_format(OrderDate, '%D %b %Y') as formatted_date FROM Orders INNER JOIN Customers WHERE OrderCustomerID = CustomerID";
$DateFormat = mysql_query($query_DateFormat, $connOriental) or die(mysql_error());
$row_DateFormat = mysql_fetch_assoc($DateFormat);
$totalRows_DateFormat = mysql_num_rows($DateFormat);

It works fine in that the format is correct, but it's only showing the first date in the recordset for each order, rather than the correct date for each order.

I'm not sure why, as other than the date format the query is the same.

My best guess is that I need to reference the OrderID, but I'm not sure of the correct syntax....?

Options: ReplyQuote

Written By
Query syntax
February 10, 2007 08:00AM
February 11, 2007 03:11AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.