DATE_FORMAT question

[mastubbs]

Hi All,

 

So I am using this query to return some values.:

$Find_Query2 = mysql_query("SELECT patients.*, addobs.*
FROM addobs INNER JOIN patients ON addobs.MRN = patients.MRN
WHERE addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN)
AND addobs.par > 6 
AND NOT addobs.hidden = 'yes'
AND COALESCE(patients.ward,'') != 'dc'
order by addobs.par ASC");

However, i one of the values ('datetime') is in the php datetime format yyyy-mm-dd hh:ii:ss. I want to return this in a different format dd/mm/yyyy hh:ii:ss. I tried using DATE_FORMAT as thats what i usually use to do this but i think my syntax is wrong as it is returning no results for 'datetime' in the way i am using it. I think im getting confused with my brackets somewhere.

$Find_Query2 = mysql_query("SELECT patients.*, addobs.*
FROM addobs INNER JOIN patients ON addobs.MRN = patients.MRN
WHERE addobs.datetime = (SELECT DATE_FORMAT(MAX(OLAST.datetime), '%d/%m/%Y %H:%i:%s') FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN)
AND addobs.par > 6 
AND NOT addobs.hidden = 'yes'
AND COALESCE(patients.ward,'') != 'dc'
order by addobs.par ASC");

Can anybody tell me what im doing wrong here?

 

Thanks in advance for any help,

 

Matt

[ginerjm]

I"m thinking you need an "as xxx" clause after the DATE_FORMAT in order to give it a simple name.  Did you dump a record to see what fields you did get from this query?

[kicken]

If you want the value returned to PHP in a different format, you need to use DATE_FORMAT on the column in the columns of the first SELECT, not in the sub-query.

 

SELECT patients.*, addobs.*, DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as datetimeFormatted 
FROM addobs INNER JOIN patients ON addobs.MRN = patients.MRN
WHERE addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN)
AND addobs.par > 6 
AND NOT addobs.hidden = 'yes'
AND COALESCE(patients.ward,'') != 'dc'
order by addobs.par ASC

That will select the field addobs.datetime with the new format under the name datetimeFormatted. So in your PHP you'd access it as $row['datetimeFormatted']