Re: To_Char Problem

Why would you want to do that - convert two dates into a char and then compare them ? This is not a problem but it is how char comparisions work. They are compared char by char to see which one is greater on the ASCII chart.

See this : SQL > select '1' from dual where '3' > '10';

'
-
1

Babu

karthikeyan S <skarthik_at_globalsw-in.com>@fatcity.com on 08/14/2002 11:53:53 AM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

Hi All,

I am using the to_char function in the following query. But it treats the date '31/12/2001' as greater than '01/01/2002'. Is there any solution to fix this problem?

 select distinct(a.default_type_id), a.new_val  from amend_default_value a, amend_default_value b

 where a.effective_from <= sysdate and
       a.effective_to    >= sysdate and
       a.group_id = '942'       and
       a.default_type_id = b.default_type_id     and
       to_char(a.updated_date,'dd/mm/yyyy hh:mi:ss') =
       ( select max(to_char(updated_date,'dd/mm/yyyy hh:mi:ss'))
       from amend_default_value c
       where c.effective_from <= sysdate and
       c.effective_to    >= sysdate and
       c.group_id = '942' and
       c.default_type_id = b.default_type_id);

Thanks in advance.

regards,
Karthik

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: karthikeyan S
  INET: skarthik_at_globalsw-in.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Babu.Nagarajan_at_Cummins.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).