Re: ORA-06502

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Wed, 4 Feb 2009 12:41:08 -0800 (PST)
Message-ID: <2f6f87f1-78a8-4f25-a94d-8ee3a0c29b92_at_t39g2000prh.googlegroups.com>



On Feb 4, 11:32 pm, "bob123" <bob..._at_gmail.com> wrote:
> Hi,
>
> I have the follwing error with the query below:
>
> OK for 01/01/2008 and 15/07/2008
>
> OK for 01/08/2008 and 30/09/2008
>
> ORA-06502 with other values
>
> Thanks for your help (9.2.0.6)
>
>
>AND TO_DATE (TO_CHAR(V$CASE_MASTER.CREATE_TIME,'dd-mon-yyyy')) >= '01/01/2008'
> AND TO_DATE (TO_CHAR(V$CASE_MASTER.CREATE_TIME,'dd-mon-yyyy')) <= '30/09/2008'

What's the purpose of all these conversions? What's the CREATE_TIME data type - a DATE? If so, you are converting a DATE to a VARCHAR2 and then immediately back to DATE (without specifying the parsing mask,) which makes no sense. The conversion should actually be on the other side:

V$CASE_MASTER.CREATE_TIME >= TO_DATE('01/01/2008','DD/MM/YYYY') AND V$CASE_MASTER.CREATE_TIME <= TO_DATE('30/09/2008','DD/MM/YYYY')

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Feb 04 2009 - 14:41:08 CST

Original text of this message