Re: SQL*Loader and date/time datatypes

Try the following controlfile:

load data
truncate into table my_test
fields terminated by "," optionally enclosed by '"' (

	my_date position (1) date 'YYYYMMDD'
	, my_time position (*) date 'HH24.MI.SS'

This worked for me in Linux EE 8.1.7.1.1. However, the day part of my_time is set to the first of the current month. If you just want the time you have to strip away this part (my_time - trunc (my_time)) but then you do no longer have a date type variable but a duration (= number).

Martin

Ed Stevens wrote:
>
> 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 Tue Jul 10 2001 - 00:49:56 CDT