Re: SQL*Loader and date/time datatypes

Load the data into a "staging" table with my_datetime defined to be a varchar2 field.

After loading, copy (insert into T select from staging) and use to_date(substr()'s) to fix.

"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message news:3b49f571.13469938_at_ausnews.austin.ibm.com...
> Subject: SQL*Loader and date/time datatypes
>
> We are trying to extract data from an old DB2/2 database to load into
> an Oracle 8.1.7 database. DB2/2 has separate data types for date and
> time. So, given the original table that looks something like this:
>
> CREATE TABLE MY_TEST
> (MY_DATE DATE,
> MY_TIME TIME)
>
> We would create this table in Oracle:
>
> CREATE TABLE MY_TEST
> (MY_DATE DATE,
> MY_TIME DATE)
>
> The extracted data file looks like this:
>
> 20010624,"13.36.23"
>
> We have no trouble loading MY_DATE with no time component (running a
> test with no "time" data), but cannot get MY_TIME loaded. We would
> prefer to have no date component at all, but would be willing to use a
> default of either a fixed "magic" date or sysdate, but can't seem to
> work out the syntax for the SQLLoader control file to make this
> happen.
>
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)
Received on Mon Jul 09 2001 - 15:30:41 CDT