Re: HELP - Ordering by Date format
Date: 12 Sep 1994 16:05:00 +0100
Message-ID: <351qms$6h8_at_crocus.csv.warwick.ac.uk>
nkitchen_at_axion.bt.co.uk (Nicholas Kitchener) writes:
> I have a project that requires the output of an SQL Query to a file - easy
> enough - but the results have to be in order of DATE & TIME.
> If I do a:
> SELECT .., to_char(very_tarty_column, 'DD/MM/YY HH24:MI:SS', ..
> ..
> .. where statements etc..
> ..
> ORDER BY 2 ASC, ..;
> Oracle outputs the data correctly except for the order. I am aware that the
> to_char() uses an alpha sort & therefore 15 comes before 6, but if I sort
> by:
> SELECT .., very_tarty_column, ..
> ..
> .. where statements etc..
> ..
> ORDER BY 2 ASC, ..;
> Oracle proceeds to use the DATE only, and not the TIME section too and also
> the date is in the wrong format.
> It may have a very simple solution and I'm being thick - or - is this a
> functional problem?
In SQL*Plus, try this:
Column OrderCol NoPrint
SELECT .., to_char(very_tarty_column, 'DD/MM/YY HH24:MI:SS'), ..
.., to_char(very_tarty_column, 'YYYYMMDDHH24MISS') OrderCol, ..
.. .. where statements etc.. ..
ORDER BY OrderCol ASC, ..;
> The output of the SELECT does not matter, as I can adjust the SQL results
> parser.
> Thanks in advance,
You're welcome; not entirely sure if this is what you're after, but I hope this helps.
> Nick.
> +----------------------------+--------------------------------------------+
> | N.Kitchener_at_axion.bt.co.uk | |
+----------------------------+--------------------------------------------+
> | All options and views in the above mail do not represent those of BT |
> | but those of a 12year old hacker that has broken into my account. |
> +-------------------------------------------------------------------------+
Hank Robinson
Oracle DBA
University of Warwick
Received on Mon Sep 12 1994 - 17:05:00 CEST