5 Replies Latest reply: Mar 3, 2011 2:33 AM by BluShadow RSS

    using NVL for date

    802808
      I am creating a view and one of the columns is this:
      (b.startdate || ' To ' || b.enddate) salesDateRange
      however when I was trying to handle the null value of date, I found the following problems,

      case 1
      (nvl(b.startdate, 'NA') || ' To ' || b.enddate) salesDateRange

      when i use nvl, I am able to create the view, but when I retrieve the data, I get this error msg.
      ORA-01858: a non-numeric character was found where a numeric was expected
      01858. 00000 - "a non-numeric character was found where a numeric was expected"
      *Cause:    The input data to be converted using a date format model was
      incorrect. The input data did not contain a number where a number was
      required by the format model.
      *Action:   Fix the input data or the date format model to make sure the
      elements match in number and type. Then retry the operation.

      is nvl only available for numerical value?
        • 1. Re: using NVL for date
          Ganesh Srivatsav
          Assuming start date is of data type date, Try using something like this
          NVL(TO_CHAR(b.start_date,'MM/DD/YYYY'),'NA')||' To '|| NVL(TO_CHAR(b.end_date,'MM/DD/YYYY'),'NA') -- Replace 'MM/DD/YYYY' with any format that you want.
          g.

          Edited by: G. on Mar 2, 2011 11:41 PM
          • 2. Re: using NVL for date
            Lokanath Giri
            As the display of date format is not defined so take the default format
            SQL> SELECT * 
                      FROM emp;
            
                 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
            ---------- ---------- --------- ---------- --------- ---------- ----------
                     1 FORD       ANALYST         7566 03-DEC-81       3000
                     2 MILLER     CLERK           7571 23-JAN-82       1300
            
            SQL>SELECT (nvl(CAST(b.hiredate AS VARCHAR2(12)), 'NA')) || ' To ' || CAST(b.hiredate AS VARCHAR2(12)) hire_date FROM Emp b
            /
            HIRE_DATE
            ----------------------------
            03-DEC-81 To 03-DEC-81
            23-JAN-82 To 23-JAN-82
            So your as per your requirement
            (nvl(CAST(b.startdate AS VARCHAR2(12)) ,'NA') || ' To ' || CAST(b.enddate AS VARCHAR2(12)) salesDateRange
            • 3. Re: using NVL for date
              Braam
              Hi
              Try to convert the date values to char values by using TO_CHAR() function

              try this : SELECT NVL(TO_CHAR(HIREDATE),'ORACLE')HIRE_DATE FROM EMP;

              and output like this: HIRE_DATE

              17-DEC-80
              20-FEB-81
              • 4. Re: using NVL for date
                Etbin
                is nvl only available for numerical value?
                No.
                try
                select to_date(null) null_date,dump(to_date(null)) dump_null_date from dual
                Dates should be stored as dates (even when null)

                Regards

                Etbin
                • 5. Re: using NVL for date
                  BluShadow
                  user8944947 wrote:
                  I am creating a view and one of the columns is this:
                  (b.startdate || ' To ' || b.enddate) salesDateRange
                  however when I was trying to handle the null value of date, I found the following problems,

                  case 1
                  (nvl(b.startdate, 'NA') || ' To ' || b.enddate) salesDateRange

                  when i use nvl, I am able to create the view, but when I retrieve the data, I get this error msg.
                  ORA-01858: a non-numeric character was found where a numeric was expected
                  01858. 00000 - "a non-numeric character was found where a numeric was expected"
                  *Cause:    The input data to be converted using a date format model was
                  incorrect. The input data did not contain a number where a number was
                  required by the format model.
                  *Action:   Fix the input data or the date format model to make sure the
                  elements match in number and type. Then retry the operation.

                  is nvl only available for numerical value?
                  In addition to the answers already given, you have to consider that NVL is an overloaded function, designed to deal with different datatypes. It accepts 2 arguments and the version of NVL that is used depends on the arguments given (that's the nature of overloading - look it up on the internet if you're not sure).

                  So, there is defined e.g.
                  NVL({noformat}<number arg>, <number arg>{noformat})
                  NVL({noformat}<varchar2 arg>, <varchar2 arg>{noformat})
                  NVL({noformat}<date arg>, <date arg>{noformat})
                  etc.
                  In your example, as the first argument you are passing in is a date, Oracle is opting to use:
                  NVL({noformat}<date arg>, <date arg>{noformat})
                  But your second argument is not a date, it's a varchar2.
                  However, Oracle is clever and can perform "implicit" conversions between datatypes i.e. it will try and convert datatypes without you actually telling it to. (It's generally good coding practice to do it yourself though).

                  So, in you case Oracle is taking your varchar2 string "NA" and trying to implicitly convert this to a date, using the only way it knows how, which is to apply a date format mask as defined by the NLS_DATE_FORMAT parameter for the session. For example, internally it will be trying to do the equivalent of:
                  TO_DATE('NA','DD-MON-YYYY')
                  (assuming your NLS_DATE_FORMAT parameter is set to DD-MON-YYYY)

                  If we try that ourselves, we get...
                  SQL> select to_date('NA','DD-MON-YYYY') from dual;
                  select to_date('NA','DD-MON-YYYY') from dual
                                 *
                  ERROR at line 1:
                  ORA-01858: a non-numeric character was found where a numeric was expected
                  ... which is the same error you were getting.

                  So, in order for your NVL to work you would either need to supply a DATE as the second argument (some default date to use if the first argument is null), or some varchar2 string that represents a date in the correct format as per your NLS_DATE_FORMAT setting (dangerous because if the NLS_ setting changes then your code can suddently error), or you provide the first argument as a VARCHAR2 string instead of a date (using TO_CHAR), so the the function overloading causes Oracle to choose the varchar2 argument version of NVL, for which 'NA' is a perfectly valid second argument.