2

I am using sqlldr to transfer data from a flat file (.txt) to oracle db. As for dates I am using "to_date(:born, 'DDMMYY')", where born is the column. How can I specify a B.C. year (e.x: 01-01-1000BC) in my flat file so that it can be transfered to my oracle table?

Bendemann
  • 735
  • 11
  • 31
  • 2
    BC is just a negative year with a change to your string format Example: https://stackoverflow.com/questions/6779521/how-do-i-insert-a-bc-date-into-oracle or `to_date('-1000/01/01','syyyy/mm/dd')` – xQbert May 29 '18 at 17:13
  • Yes, I have read that, but how do I specify it in my .txt file? I am inputing the date in the text file and then it is transfered by sqlldr to db table. I am specifying the date like: 01011990, for 01-01-1990. What should I write for the B.C. value? – Bendemann May 29 '18 at 17:23
  • 1
    How about load it as a varchar(20) and then after import run the to_date on it adding a new field then drop original field? I'd have to research the sqlldr more to know if there's a way to handle the BC in a single step; can the date be -10000101 SYYYYMMDD? – xQbert May 29 '18 at 17:29

1 Answers1

3

You could make the string in the text file always have a BC or AD suffix (you can't omit AD in this scenario), e.g. 01011000BC and 01011990AD, and then in your control file:

"to_date(:born, 'DDMMYYYYBC')"

Or you can do the same thing with the sign option if you only want to change negative values, e.g. 0101-1000 and 19900101 and then in the control file:

"to_date(:born, 'DDMMSYYYY')"

which looks rather odd for the BC value.

Since you seem to control the format, it would be simpler and more readable to put the elements in YYYYMMDD order, e.g. -10001231 and 19901231 (changing the dates to help distinguish between months and days in the string) and then:

"to_date(:born, 'SYYYYMMDD')"

which is essentially what @xQbert suggested.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318