0

I have created a sql query in which I'm passing a parameter to the section shared below

BETWEEN 
           NVL(TO_CHAR(:DATE1, 'DD-MON-YYYY'), DATETIME) 
           AND 
           NVL(TO_CHAR(:DATE2, 'DD-MON-YYYY'), DATETIME)

When I run this query with the data type it works fine but when I run this query with VARCHAR2 parameter it gives the following error:

[Error] Execution (9: 25): ORA-01722: invalid number

When I run this query with null, the query shows up all the records. actually I need to run this query using a STRING parameter not with the DATE dataype so when I pass the parameter to the same query in toad it will work fine even with the null values.

Here is my complete query:

SELECT rownum,ACCOUNT_NO,CUSTOMER_NAME,CARD_NO, SOURCE, ATM_ID, ISSUER_BANK_NAME,ASE.STATUS_DESC, CARD_TYPE, CARD_RESP,
DATETIME,BR_INPUT_DATE BR_ACTIVITY
FROM ATM_RCCM, ATM_STATUS_ERRORS ASE
    where ASE.STATUS_DESC = NVL(:PT,  ASE.STATUS_DESC) 
        AND BR_TRACKING_STATUS = ASE.STATUS_CODE
            AND CARD_TYPE = NVL(:CT, CARD_TYPE)
            AND DATETIME
    BETWEEN 
           NVL(TO_CHAR(:DATE1, 'DD-MON-YYYY'), DATETIME) 
           AND 
           NVL(TO_CHAR(:DATE2, 'DD-MON-YYYY'), DATETIME)

Also note that the DATETIME column has the default datatype of VARCHAR2.

BDL
  • 21,052
  • 22
  • 49
  • 55
casper
  • 45
  • 1
  • 7

2 Answers2

1

Elementary dear Watson: TO_CHAR converts dates to varchar2. Why do you expect it to work if you give it the wrong data type? When you give it a varchar2 input, it will (stupidly in my opinion) try to convert it to a date first, instead of throwing a compilation error. It will use your session's NLS_DATE_FORMAT though, NOT the format model you have in TO_CHAR. So in your case, it can't and it throws a runtime error.

The comparison is wrong anyway; it is pretty clear you want to compare dates, not strings. So why are you converting the dates to strings first, and then compare? With string comparisons, 12-JAN-2012 is before 9-MAR-1993.

Did you just mean to use TO_DATE, not TO_CHAR? Give it a try and see what happens! Just make sure you wrap DATETIME within TO_DATE too, with the appropriate format model.

0

It has to be an issue with the parameters you are entering. Likely your :DATE1 or :DATE2 are in the incorrect format, or you may have an alpha character in CT or PT

David Brenchley
  • 86
  • 7