Order By on to_char date

[Lead_Head]

Hello all,

I'm trying to order a set of dates that return as part of a union query I'm writing in SQL (which will go in to a PL/SQL script).

The query is part of a UNION because I'm putting Totals underneath the result set and I'm matching the date to the text 'TOTALS' hence the 'to_char'.

My output is like:

01-AUG-09
01-SEP-09
01-OCT-09
02-AUG-09
02-SEP-09
02-OCT-09
03-OCT-09
03-AUG-09
03-SEP-09

etc.

What I want is it in date order e.g.
01-AUG-09
02-AUG-09
03-AUG-09
01-SEP-09
02-SEP-09
03-SEP-09
01-OCT-09
02-OCT-09
03-OCT-09

 

[djkav]

I don't think you can date order if its been treated as a string.

You could create a new field on the fly to store the date as a date for the sole purpose of the order by.

 

[Engram]

I don't think you can date order if its been treated as a string.

Correct. It will do an alphanumeric sort instead.

 

[topbanana]

Just select the actual date as well, and sort by that