Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

date_format and order by date problem

793966Aug 26 2010 — edited Aug 27 2010
Hi,
I have a problem with taking the resultset as sorted by date.

example1:
SELECT
T.foreign_network_id as "Network ID",
date_format(T.ARRIVAL_DATE,'%d-%m-%Y') as "Date",
sum(case when((networks_rejected = 0)
and (T.sor_reapply = 'N')
and (T.sor_reason in ('NP', 'NU', 'NT', 'HT')))
then T.CNT else 0 end) as "Before Steering",
sum(case when(sor_reason in ('NP', 'HT', 'T2', 'TM', 'NM', 'MM', 'IPMM', 'NPMM'))
then T.CNT else 0 end) as "After Steering"
FROM ST_INC T,
NETWORKS N,
ZONE_NW_MAP ZN,
ZONES Z,
PAYMENT_TYPE PT
WHERE T.FOREIGN_NETWORK_ID = N.NETWORK_ID
AND ZN.NETWORK_ID = N.NETWORK_ID
AND Z.ZONE_ID = ZN.ZONE_ID
AND PT.id = T.ptype
AND opcode = 2
AND sor_action is not null
and Z.ZONE_ID = 269

group by
date_format(T.ARRIVAL_DATE,'%d-%m-%Y'),
T.foreign_network_id
order by date_format(T.ARRIVAL_DATE,'%d-%m-%Y')

There is no problem with example 1 however it sorts the result set as if the date field is a string. I know this should be because I use date_format with order by. When I delete and only use T.ARRIVAL_DATE, I get "not a group by expression" error. And the reason why I use date_format is that sometimes I want to retrieve different format such as '%m-%Y' or only '%Y'. So I am required to use date_format, but then I lose the chance to sort by the actual date. I tried to select "T.ARRIVAL_DATE" as another extra colum and sort with respect to that. But this time I lost tha chance to select it again in the date_format function. Can anybody help me, I am messed up here?

Edited by: user13378594 on Aug 26, 2010 3:00 PM

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 24 2010
Added on Aug 26 2010
5 comments
1,532 views