Please help with Oracle 9.2 datatime sort

[LadyDi02]

Hello once again experts,

I am really crunched for time and I know this will be an easy on for you guys I just can;t seem to get my head around it. I have the following query

select ID,
          to_char(Function_Convert(SDate,'EDT'), 'MON DD, YYYY HH24:MI:SS AM') AS  SDATE,
from Table A
where ID='123'
order by to_date(Function_Convert(SDate,'EDT'), 'DD-MON-RR');

Please not the Function_Convert is taking a number and converting that number to a date in format dd-mm-rr.

When I run this query I expect the output to sort the SDate field so it shows in asc order of all the dates and time however what I am seeing is the following

123 Mar 08, 2003 12:07:06 PM
123 Mar 08, 2003 12:04:42 PM
123 Mar 08, 2003 12:20:06 PM


Any ideas why it is doing this? For the life of me I can't figure it out. I am using oracle 9.2. Thanks again

 

[carp]

Your ORDER BY does not include the hours/minutes/seconds, so after 'DD-MON-YY', the sorting stops and you can get random times. Try 'ORDER BY 2' and see what you get.

 

[jimirvine]

Please not the Function_Convert is taking a number and converting that number to a date in format dd-mm-rr.

If function convert is returning a date, then why wrap a to_date around it in the order by clause? Just order by
Function_Convert(SDate,'EDT')

Try 'ORDER BY 2' and see what you get.

You would get a sort on a text field So you would end up with April, August, etc

 

[LadyDi02]

Sorry for the late response. Thanks so much for everyone's help. I removed the to_date and all is well. Thanks again.

 

[LadyDi02]

Wow now I am stumped with the requirments.

I have a subset of data using oracle 9.2

Date name SRT Status ValueINT
03-Jun-05 Jim C 123 WIP 0
03-Jun-05 Dino B 123 WIP 0
03-Jun-05 Jim C 123 Closed 1
03-Jun-05 Sal M 456 WIP 0
03-Jun-05 Dave G 456 WIP 0
03-Jun-05 Sal M 456 Closed 1

What I need to do, hopefully in a case statement or even a function(as my query is a bunch of selects and case statements), is look for all records where the field heading status is 'closed'. When you find the 'closed' status if name heading in that record is the same as the name in the first record of that SRT(look closely how I ordered the SRT numbers) then give the value of 1 in another column(ValueInt). Hope that makes sense. I am trying to compare values from the last record in an ordered by SRT, date to the first record in the order. The order by cannot change in this query.

Therefore looking for the first closed in the recordset I can see that it occurs in the following record.

03-Jun-05 Jim C 123 Closed 1

I will then compare this record to the first record of that SRT group(123) and I will find

03-Jun-05 Jim C 123 WIP 0

Well the name in this record is the same as the name in the closed record so therefore I will increment the field ValueInt for the closed record to 1. I will then proceed to look at SRT 456 and look for the closed status and compare it to the first record of SRT 456...etc etc
If someone can figure this out your a genious. Thanks again.