Home » RDBMS Server » Server Utilities » How to load date format field using sql* loader ---URGENT
How to load date format field using sql* loader ---URGENT [message #365805] Mon, 07 February 2000 10:44 Go to next message
karunakar pabba
Messages: 5
Registered: February 2000
Junior Member
Hi
I have date format in my data file like 'mmddyy'and if i transfer data using sql* loader its loading but the year is loading as 2000 instead of 19xx,and also i need to use trailing nullcols in my control file as there is no data for some of columns.
Should i use to_char or to_date function in my control file for loading date column please send me the syntaxes with examples.
My data file is having ',' as field separator and also enclosed in '"' for character data type field.

PL.. SEND ME THE SOLUTION... AT THE EARLIEST.

Thanks in advance

-- karun
Re: How to load date format field using sql* loader ---URGENT [message #365807 is a reply to message #365805] Mon, 07 February 2000 14:08 Go to previous messageGo to next message
Roger Cornejo
Messages: 5
Registered: January 2000
Junior Member
I don't know if this will help or not, but consider the following SQL:
select to_char( (to_date('12/31/99','MM/DD/YY')-36525), 'DD-MON-YYYY')
from dual;
What it is doing is subtracting 36525 days from the date which will work for input date strings in 1999.
Perhaps there is a better solution. It seems like a brute force method, but you could come up with an elaborate scheme to subtract the correct number of days depending on the input year. Alternatively, reformat the input file. to specify 4 character years.

Regards,
Roger ---|-
Re: How to load date format field using sql* loader ---URGENT [message #365809 is a reply to message #365807] Mon, 07 February 2000 14:46 Go to previous messageGo to next message
karunakar pabba
Messages: 5
Registered: February 2000
Junior Member
sorry friend this will not work out for me

karunakar
Re: How to load date format field using sql* loader ---URGENT [message #365810 is a reply to message #365807] Mon, 07 February 2000 14:46 Go to previous messageGo to next message
karunakar pabba
Messages: 5
Registered: February 2000
Junior Member
sorry friend this will not work out for me

karunakar
Re: How to load date format field using sql* loader ---URGENT [message #365816 is a reply to message #365809] Tue, 08 February 2000 06:32 Go to previous message
Ales
Messages: 11
Registered: February 1999
Junior Member
Hi,

if you setup NLS_DATE_FORMAT='DD.MM.RRRR' on the client AND server then you can load two digit year by rule 50-99 is 19, and 00-49 is 20.
You can also use TO_DATE function in ctl file instead;

date_field DATE "TO_DATE (:date_field,'DD.MM.RRRR')",

Ales
Previous Topic: ORA-00222 Failed to export database
Next Topic: Re: dump table records to 'insert into xxx values ( ....)' statements ?
Goto Forum:
  


Current Time: Mon May 23 21:48:06 CDT 2022