-1

I have database in access with a text field '1000306' - it is reporting date. I created table:

create table TT
(
DT VARCHAR2(20)
);

I loaded data by sql loader.

How can I get date from it? Should I convert varchar2 to DATE in Oracle? Or should I do it before loading data?

I tried:

create table TT
(
DT DATE
);

but it didnt load any data.

wakul
  • 1
  • 3
    *"it didnt load any data."* So what messages did Loader write in the log file? Looking at that would surely be a useful exercise? – APC Jan 28 '18 at 16:24
  • it shows: Commit point reached - logical record count 65 And then there is nothing in my table. But when I load it the same way, but choose datatype: varchar2 it loads without any issue. – wakul Jan 28 '18 at 16:30
  • 2
    What sort of date is `'1000306'`??? tenth of March 2006? sixth of March 2010? third of October 1906? Storing dates without a century or other formatting is just a recipe for data corruption. – APC Jan 28 '18 at 17:16
  • The only formula I can think that gives a reasonable date is if it represents hours since 1st January 1900, so 1000306 gives 11 Feb 2014 10:00. Am I close? – William Robertson Jan 28 '18 at 22:40

1 Answers1

1

In Oracle, that column should have the DATE datatype.

Control file (used by SQL*Loader) should then convert input value into a valid date format, using the TO_DATE function, such as

load data
infile ...
fields ...
( ...
  dt "to_date(:dt, 'yyyymmdd')",    --> this
)

I don't know what date value you mentioned (1000306) represents (but I'm sure you do) so - just modify date format and, hopefully, you'll manage to load data.

As of your success when loading it into a VARCHAR2 column - no wonder, as it accepts any string (which 1000306 is), but you should NOT store date values into VARCHAR2 column, ever.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57