[SOLVED] Query not ordering results

[CptnChainsaw]

Hi all, I'm trying to list phone calls in the order of priority.  My 'callback' field is of type 'datetime'.  My order by clause isn't making any difference.  I need the data ordered by date and time.

 

Any ideas what's wrong with this:

 

SELECT id, companyname, contactname, tel, DATE_FORMAT(callback, '%e/%c/%Y %l:%i %p') AS callback FROM companies WHERE callback > 0 ORDER BY callback ASC

 

 

Thanks in advance.

 

CptnChainsaw

[CptnChainsaw]

I've worked out that the DATE_FORMAT() function is causing this query to fail.  Anyone know how I can still use this query with the function?

[effigy]

What error message are you getting?

[CptnChainsaw]

Problem solved:

 

SELECT id, companyname, contactname, tel, STR_TO_DATE(DATE_FORMAT(callback, '%e/%c/%Y %l:%i %p'), '%e/%c/%Y %l:%i %p') AS callback FROM companies WHERE callback > 0 ORDER BY callback ASC

[CptnChainsaw]

My last query works but it doesn't display the date with the formatting I expect (dd/mm/yyyy hh:mm am/pm) instead it outputs it as yyyy-mm-dd hh:mm:ss

 

 

Any idea why?

 

 

 

Cheers again,

 

CptnChainsaw

[effigy]

I see no reason to double up the format. Before you added STR_TO_DATE, what errors messages were you getting?

[CptnChainsaw]

I agree it should've worked straight away as I'm using a datetime field type.  However it just didn't order the results.  Calling the function to explicitely convert to a date type did the trick  - not sure why though.  I wasn't getting any errors it just didn't produce the correct results.

 

Thnks for your replies btw

 

CC