how to store double colon values in oracle database table

Question

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'

Answer 1

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

Answer 2

If it is part of a date/time stamp then you could store it as DATE or TIMESTAMP if you could add the date component. Oracle doesn't have just a TIME data type.

If you can't add a date component to it, then assuming it is a time interval you could convert it to seconds or microseconds (lose the colons) and store it as a NUMBER.

If you want to maintain the exact formatting as shown, your only option is to store it as text using VARCHAR2 or something similar.