5 Replies Latest reply on Dec 20, 2013 8:53 PM by Srini Chavali-Oracle

    implicit conversion


      doesnot oracle implicitly convert varchar(2) to date in round and trunc functions?

        • 1. Re: implicit conversion
          Brian Bontrager

          Date? what would rounding a date do?

          Yes there is implicit conversion to the most appropriate data type.


          select round('12345.67') from dual;



          In this case, the appropriate data type is NUMBER.

          • 2. Re: implicit conversion
            Frank Kulash




            f7218ad2-7d9f-4e71-ba26-0d6e4b38f87e wrote:


            doesnot oracle implicitly convert varchar(2) to date in round and trunc functions?

            ROUND and TRUNC do not take VARCHAR2s fior the 1st argument.  They take either NUMBERs or DATEs.  If you try to call them with any other data type, you are asking for trouble.  Sometimes you get what you ask for.  There is no reason to call either with the wrong type of argument.


            If you do call ROUND or TRUNC with a VARCHAR2 1st argument, even though you know it's a terrible idea, Oracle will try to convert it.  Whether it tries to convert it to a NUMBER or a DATE may depend on the string and/or your NLS settings, though it seems to always try to conver to a NUMBER.


            Implicit conversions are never necessary.  Explicit conversions are always simple.  Always use the correct datatype.  If you must convert from one datatype to anopther, use an explicit conversion.

            • 3. Re: implicit conversion
              Brian Bontrager

              Answered my own question about rounding dates...


              (with my NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS')


              select round(to_date('01-JAN-2013 15:51','DD-MON-YYYY HH24:MI')) from dual;
              2013-01-02 00:00:00
              • 4. Re: implicit conversion

                whenever i am trying to do round('25-JUL-2003','MONTH') from dual; its generating error because it is not  converting it into DATE where as when i am doing select round(sysdate,'MONTH') from dual; gives no error