[SOLVED] Changing Date format while returning data?

[Gnub]

Is there a way to change the format of a Date during a query?

 

Example.

 

The Date is in American Format MM/DD/YYYY

 

When im returning the data, i want the date in Euro Format, DD/MM/YYYY

 

Is there a way to do this during the main query(when asking for specific info) or will i be better off finding a way of doing it somewhere on my php script?

[trq]

Is there a way to change the format of a Date during a query?

 

Use the mysql function DATE_FORMAT().

[Gnub]

I've had a bash with this function, but it's not liking my attempts.

 

$sql = "Select * FROM `Teletext` WHERE DepartureDate BETWEEN '$DateFrom' AND '$DateTo' Order BY Price ASC;"

 

Got an idea where i can add that function?

 

Tried before @DepartureDate@ and before @ORDER BY@ .

[trq]

You shouldn't use the wildcard ( * ) to select all data, but name your fields explicitly. A simple example.

 

SELECT DATE_FORMAT(fldname,'%d/%m/%y') AS formated_date FROM tblname;

[Gnub]

Round 3...

 

Had yet another bash at this function, this is the query, however the date does not change when i check the results.  it remains YYYY-MM-DD, when i want it at DD-MM-YYYY.

 

Any help would be great.

 

SELECT `Teletext`.*,DATE_FORMAT(`DepartureDate`, '%d %m %Y') AS revised_date, WHERE DepartureDate BETWEEN '$DateFrom' AND '$DateTo' Order BY Price ASC;

 

Should i be using the `DepartureDate` when echo'ing the results?  im completely new at this revised date, and not using the (*) wild card.

 

cheers in Adv.

[JJohnsenDK]

Why not convert the date in a php function?

 

if (!function_exists('showdate2')) {
function showdate2 ($date) {
	$year = substr($date,0,2);
	$month = substr($date,3,2);
	$day = substr($date,6,2);
	$rest = substr($date,9,5);
	return $day . "/" . $month . "-" . $year ." ".$rest;
}
}

[Lodar]

No you need to use the new column it is now referenced as when you echo the results, so it will be revised_date you need to use. By echoing DepartureDate you are getting the original value before you applied the formatting.

 

As thorpe has said, you really need to type in the names of the fields you require and not use *, so as an example it would be

 

   SELECT col1, col12, col3 FROM table1 WHERE col1 BETWEEN '$here' AND '$there' ORDER BY col1 desc;

[Gnub]

cheers for the help.  Managed to solve it, had to put in that 'revised_date' . 

 

Kudo's to you guys.