1 Reply Latest reply: Jan 8, 2014 5:39 AM by _Phil RSS

    Convert Time

    Moazam Shareef

      hi guyz,

       

      I imported some data from Microsoft Access into Oracle where some time field is display as 210539  as time i want to convert this time in property format IE (21:05:39 HH24MISS) but when i'm using the below query it showing error anything wrong with my query? i

       

      SQL> SELECT TO_CHAR(101550,'HH24:MI:SS')FROM DUAL;
      SELECT TO_CHAR(101550,'HH24:MI:SS')FROM DUAL
      

                            *

      ERROR at line 1:

      ORA-01481: invalid number format model

       

      i'm using oracle 10g.

        • 1. Re: Convert Time
          _Phil

          You're trying to convert a number to a string while using a datetime mask - this isn't allowed.  To use 'HH:MI:SS', the value to be converted must be a datetime type.

           

          e.g. this will work:

           

          SELECT TO_CHAR(to_date('101550', 'HH24MISS'),'HH24:MI:SS')FROM DUAL

           

          ...because now we've told Oracle that 101550 is actually a datetime in the format 'HH24MISS'.