How to extract the time component from timestamp column in SQL Loader's control file?

Question

Hoping that someone may help me out.

Oracle Version: 19.3.0

CREATE TABLE test
(
  DATE_STAMP         DATE,
  TIMESTAMP          TIMESTAMP(6)
)

Sample data in csv format is as follows

cat test.csv

2007/05/13,16:35:24

2007/05/13,18:00:51

Control File:

OPTIONS(skip=1)
LOAD DATA
        INFILE 'test.csv'
        BADFILE 'test.bad'
        INSERT INTO TABLE test
        FIELDS TERMINATED BY ','
        (
         date_stamp,
         timestamp      TIMESTAMP "HH24:MI:SS.FF2"
        ) 

While the data is loaded in the table, but it is not in the correct format.

Actual Result:

select DATE_STAMP, TIMESTAMP from test;

13-MAY-07 9/1/2023 4:35:24.000000 PM
13-MAY-07 9/1/2023 6:00:51.000000 PM

Note: For some reason the "9/1/2023" is being auto pre pended to the TIMESTAMP column.

Desired Result:

13-MAY-07 4:35:24.000000 PM
13-MAY-07 6:00:51.000000 PM

Thanks in advance.

rogers7942

Answer 1

Looks like you got it wrong.

Timestamp datatype - in Oracle - contains date and time components, not only time.

Data Types documentation (search for "timestamp") says:

Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field.

In other words, that's similar to date datatype which doesn't contain fractional seconds.

If you don't specify date (while converting string to timestamp), Oracle sets it to first of current month:

SQL> alter session set nls_timestamp_format = 'dd.mm.yyyy hh24:mi:ss.ff3';

Session altered.

SQL> select to_timestamp('22:36:18', 'hh24:mi:ss') result from dual;

RESULT
---------------------------------------------------------------------------
01.09.2023 22:36:18.000

(Today is 14.09.2023).

When you store timestamp value into a table, you then choose to display it differently, e.g. only date component or only time component, in any format you want. For example:

SQL> create table test (col timestamp);

Table created.

SQL> insert into test values (systimestamp);

1 row created.

SQL> select * from test;

COL
---------------------------------------------------------------------------
14.09.2023 22:38:33.233

SQL> select to_char(col, 'hh:mi:ss.ff3 pm') display from test;

DISPLAY
---------------
10:38:33.233 pm

SQL>

If you insist on storing only timestamp value, you'll have to use varchar2 datatype and store value as a string, and that's one of bad things you could ever do. Never store date/timestamp values into anything but appropriate datatypes. Storing them as strings will cause problems, sooner or later.