Doubt in Order by DATE Query

[ScarletPimpernal]

Hi Friends,

I have a table with field

Id--vDate
-------------
1--3/1/2007 12:00:00 AM
2--4/1/2007 12:00:00 AM
3--4/12/2007 5:28:15 PM
4--3/1/2005 12:00:00 AM

SELECT DATE_FORMAT(vDa te,'%b-%Y-%m'),ID FROM TEST ORDER BY DATE_FORMAT(vDa te,'%b-%Y-%m') DESC;

The query above doesn't give me the correct answer.

Can anyone tell me can i get the ascending order on the based date field when iam formating that field using DATE_FORMAT(vDa te,'%b-%Y-%m').

Thanks,
Scarlet

[michaelb]

What happens if you change your query like this:

SELECT DATE_FORMAT(vDate,'%b-%Y-%m'), ID FROM TEST 
ORDER BY 1 DESC;

[code green]

Please follow standard practice when writing SQL. Reserved words in capital, others small case throughout.
DATE_FORMAT returns a string so the recordset will be ordered by the rules governing string order. So it does give you the correct answer.

The query above doesn't give me the correct answer

I have to assume that you want your result in date order then formatted as a string. Tricky. I am not sure. When i am faced with such problems I cheat a little. I create a temporary table, dump my results in there then take them out again. This allows space for a little more ordering of data. Try

CREATE TABLE dateorder (SELECT date,id FROM test ORDER BY date) then
SELECT DATE_FORMAT(vdate,'%b-%Y-%m'), id FROM TEST

Not tested but think it will work

[masdi2t]

What happens if you change your query like this:

SELECT DATE_FORMAT(vDate,'%b-%Y-%m'), ID FROM TEST 
ORDER BY 1 DESC;

or maybe
SELECT DATE_FORMAT(vDa te,'%b-%Y-%m'), ID FROM TEST
ORDER BY vDate DESC;

i guess it will work if your 'vDate' is Date/ Timestamp field

[code green]

I thought about this one myself masdi2t, but doubted it would work

SELECT DATE_FORMAT(vDate,'%b-%Y-%m'), ID FROM TEST 
ORDER BY vDate DESC;

I thought ORDER BY orders the result after SELECTing from the table rather than before.