Format Date from query

Help!
Have a mySQL query complete with INNER JOIN. One of the results is in the form of a date, and i want to display that date as MM,DD,YY. Now i can do that on a simple query getting one record containing the date using date_format(lastFlown, '%M %e, %Y') etc. But can't reproduce it on a large query such as below:
-----------------------------------------------------------------------------------
$query_rsAircraftFlown = sprintf("SELECT personal.idno, personal.email, aircraftFlown.recognId,
aircraftFlown.aircraftType, aircraftFlown.hoursP1, aircraftFlown.idno, aircraftFlown.hoursP2,
aircraftFlown.lastFlown, aircraftFlown.trainingPosition
FROM personal INNER JOIN aircraftFlown ON personal.idno = aircraftFlown.recognId WHERE email = '%s'", $colname_rsAircraftFlown);
------------------------------------------------------------------------------------
lastFlown is the date i am trying to format.
Can anyone help me.
Many thanks

is lastFlown an Unix timestamp?

if so:


hope it helps

War Angel,
thanks for your help.
The table column just holds an inputted date as yyyy-mm-dd. I tried your code as below:


$lastflown = $row_rsAircraftFlown['lastFlown'];
$formatLastFlown = date("m,d,Y", "$lastflown");

but the result is 01,01,1970 (previously 1982-05-29 00:00:00 and i would like it to display 05-29-1982). Is there any way of doing this in the sql query?

regards

how is lastFlown determined?

date() can only format unix timestamps. so when you insert lastFlown you would insert the current time by doing

$lastFlown = time();
insert into db....

then you can format it with the date() code I gave you when you Select it from the db.

lastFlown is inserted into the database via a form.

so when the person submits the form that moment in time is lastFlown?

no, its a date they can enter themselves

I would just output what they input, not changing, since they could input anything.

Depending on what your reason for the date, you may want the computer or server to assign the date for you... can bring up other issues, like the different time zones, etc.

I'm still just learning php, but I've used time() before for a guest book and had to format it for different time zones, it worked well. But if the user inputs the date, you may have trouble, unless they ALL do it the same way each time.

if its user inputted the only way i can think of is making lastFlown varchar and having them input the date like 11/11/02 and output it without any formatting.

I need the date to be searchable, and to make it user friendly for the user, i used three drop down lists (day,month,year) and then run this:
-----------------------------------------------------------------
$dash = "-";
$lastFlown = $lastFlownYear.$dash.$lastFlownMonth.$dash.$lastFl ownDay;
-----------------------------------------------------------------
and then inputted $lastFlown into the database formatted for mySQL.

I have managed a work around to display the date as I required although there is probably a much cleaner way of doing it:
-----------------------------------------------------------------
//format last flown
$lastFlown = substr_replace($row_rsaircraftFlown['lastFlown'], '',11,8);
//split last Flown into pieces
$lastFlown = explode("-", $lastFlown );
$trimlastFlown = trim($astFlown [2]);
$lastFlown = "$trimlastFlown-$lastFlown [1]-$dateOB[0]";
-----------------------------------------------------------------

the //format last Flown removes the 00:00:00 from the end.
the final prodcut is mm/dd/yyyy

As i said, probably not very efficient but it works!
Many thanks for your help.
regards,

You may want to try something like this to format your dates from the db.



You can replace the F d, Y to whatever format you want.

Hope that helps.