exceloracle-databasesqldatatypesvarchar2

how to store double colon values in oracle database table

I have excel which i am trying to import in oracle database table.

Some of the values in excel consist of for example 14:39.5 with double colon. What dataype in oracle database table i should provide to store this value ?

Currently have given varchar datatype and its throwing an error during import as :

Conversion error! Value: "00:12:01.615518000" to data type: "Number". Row ignored! Value is '00:12:01.615518000'. Cannot be converted to a decimal number object. Valid format: 'Unformatted'
Solution

  • You can store it as an INTERVAL DAY(0) TO SECOND(9) data type:

    CREATE TABLE table_name (
  time INTERVAL DAY(0) TO SECOND(9)
);

    Then you can use TO_DSINTERVAL passing your value with '0 ' prepended to the start:

    INSERT INTO table_name (time)
VALUES ( TO_DSINTERVAL('0 ' || '00:12:01.615518000') );

    db<>fiddle here