Problem in Converting the date with TO_DATE function?

Try using the one below to fix it out

TO_DATE('12/31/1900','MM/DD/YYYY')

What is the difference between the symbol?

use this

 TO_DATE('12/31/1900','MM/DD/YYYY')

'MM/DD/YYYY' tells how your input string looks like. Your statement tells '-' is present in between your inputs string whereas actually it contains '/'

Hi, Actually i am uploading the data from csv file to the table through SQL loader. If i insert manually through insert command the date column is inserted correctly like '12/31/1900'. But i am uploading from csv file. At that time it is inserting '12/31/2000'. In my control file i was using like below.
END_DATE_ACTIVE "TRIM(BOTH FROM (TO_DATE(:END_DATE_ACTIVE,'MM/DD/YYYY')))

What should i do for this?

Thanks

can you try the below in your control file :

END_DATE_ACTIVE "TRIM(BOTH FROM (TO_DATE(:END_DATE_ACTIVE,'MM/DD/RRRR')))

No, i was using that one. It is not getting 1900 as year. Still it is inserting year as 2000.

can you give me your complete ctl file ? and also the data which you are trying to load ( 2 sample record which have this issue )

is your data having the 4 digits for the date column ?

sorry, for the YEAR in date column

Hi,
  Attached are the control and data file. I just changed the file extension. Becasue it is not allowing to add file with csv and ctl format.
SAMPLE.xls
SAMPLE.txt

Ya my data is having four digits for year column

create the sample table with the below commands in the database first...

create table sample_Date
( END_DATE_ACTIVE date );

select * from sample_date;

Then the command use to run the loader...I have modified the ctl file and it works fine as expected.

C:\>sqlldr userid=mis_owner/mis_owner@lngmisd1 control=sam.ctl data=sam.csv

SQL*Loader: Release 10.2.0.3.0 - Production on Thu Apr 2 15:51:16 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 5

=======================
I have attached the sam.ctl file and sam.csv file  -- renamed to .ctl to .txt and renamed .csv to .xls because of uploading issues.
sam.xls
sam.txt

The "TRIM(BOTH FROM (....)" had caused the issue and hence modified it in your .ctl file and it works fine.

Good One