SQL*Loader: Date Format Not Recognized

[sfraser]

I've googled and searched, and cannot figure out the cause of this error.

The control file has this code:

Create_Time position (174) date external 'YYYY-MM-DD HH24:MI:SS.FF6',

The data looks like this:

2005-01-24 16:57:49.861353

When I run SQL*Loader, I get:

date format not recognized

In desperation, I've tried altering the number of fractional seconds, double quotes, removing "external", using all Fs instead of FF3, dancing around my workstation while jingling my car keys.

What am I doing wrong?

Respectful thanks from a middleware admin who is dabbling in SQL

[slimdave]

To load those fractions into the same column as the date you'd need a TIMESTAMP datatype rather than a DATE, but I believe that was a 9i+ feature. So I guess you'd either have to lose the fractions or load them into a seperate column.

[DaPi]

DATE doesn't have a fractional second part - that's a TIMESTAMP.

The doc's here : http://download-west.oracle.com/docs...06.htm#1006987
(I've neverloaded timestamps myself).

(P.S. when dancing around a workstation, it's the shin-bone of an ox you should be waving )

[sfraser]

Although the error said "date", the field type in the database is "timestamp".

We figured out that the Create_Time and Convert_Time entries in the control file needed a position range, rather than just a position start.

position (174:199)

That fixed it.

Thanks for sending along the link.

Shirley

[DaPi]

Originally posted by sfraser
That fixed it.

My money was on the shin-bone of a ox

[gandolf989]

Originally posted by DaPi
My money was on the shin-bone of a ox

So that's how DBA's do things in Europe.