sort by date problem

[njaromack]

I have a sql query that formats a timestamp to the specs required, but now it does not sort properly and, not being a sql guru, I seem to be stuck. Here is a snippet of my query:

to_char((NEW_TIME(to_date('01-JAN-1970','DD-MM-YYYY')+ MB.transmit_TSTMP/86400, 'GMT', 'CST')), 'HH:MI:SS am mm/dd') AS TRANSMIT_TSTMP

The problem is that I need to order TRANSMIT_TSTMP by DESC, and when I do, it doesn't recognize that times that end with "pm" are later than "am", so 1:00 am is right next to 1:00 pm. Any help would be greatly appreciated.

 

[SonOfEmidec1100]

If you order by the date/time field it should work using the table identifier to distinguish it from the formatted string.

ORDER BY MB.transmit_TSTMP DESC

 

[njaromack]

I tried that before and the error message that I received said that MB.transmit_TSTMP was not a selected expression. Any clues how to proceed from here?

 

[ZmrAbdulla]

To represent minute normally use "n" or"nn". You have used
'HH:[Blue]MI[/blue]:SS
Is that not the problem?

Zameer Abdulla
Visit Me

 

[SonOfEmidec1100]

Dont understand, it should work. Can you try

to_char((NEW_TIME(to_date('01-JAN-1970','DD-MM-YYYY')+ MB.transmit_TSTMP/86400, 'GMT', 'CST')), 'HH:MI:SS am mm/dd') AS TRANSMIT_TSTMP, MB.transmit_TSTMP AS MySortKey
ORDER BY MySortKey

 

[njaromack]

That is exactly the way I have done it. The problem is that 1:00 am is ordered right next to 1:00 pm. The sort is alphabetical, and I need it to distinguish between am and pm.

 

[Golom]

I assume that the "to_char" function returns a text string and, far as I can tell, it's not being converted to a date so it's not suprising that the sort is alphabetic rather than datetime.

Usually references to a field alias on the Order By clause don't work (except in a UNION query) because "Order By" is processed before "Select" so the derived names are not known when Order By is processed.

Exactly how does the computed field TRANSMIT_TSTMP appear?

It looks like it should be something like

01:22:33 AM 12/25

To get the sort right you need to have that in a conventional DateTime field like this

Order By DateAdd ("s", MB.transmit_TSTMP, #01-JAN-1970#)

 

[lespaul]

well i'm going to assume that the function:

to_char

converts your "date time" to a string of characters. If so, the sort is working correctly for string values. You will need to keep it a date/time if you want it to sort it date/time values correctly.

Leslie

 

[PHV]

And this ?
SELECT
...
to_char((NEW_TIME(to_date('01-JAN-1970','DD-MM-YYYY')+ MB.transmit_TSTMP/86400, 'GMT', 'CST')), 'HH:MI:SS am mm/dd') AS TRANSMIT_TSTMP
...
ORDER BY to_char(MB.transmit_TSTMP, 'HHMISS') DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[njaromack]

Well, thank you for all of your help and ideas, but my team lead has just decided that the entire query is wrong and he wants to rework it and then give it back to me to format, so... maybe some other time. Thanks!