-
Problem with date format
I have table with a column defined like
INSTATE_DATE DATE
I have to laod the data frm the csv file to this table. my csv file has data 00/00/0000. Even though it is an invalid date
I have load this date to my oracle table. I have an option of loading a default value say 01/01/2010 everytime i come across 00/00/0000.
I tried to modify the control file like
INSTATE_DATE DATE "replace(:INSTATE_DATE,'00/00/0000','01/01/2010')"
Even then im getting the error "ORA-01821: date format not recognized"
Please let me know if there are any other way to achive this.
-
Try...
Code:
INSTATE_DATE "decode(:INSTATE_DATE,'00/00/0000','01/01/2010',:INSTATE_DATE)"
... if still having datatype issues add to_date() function.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
select
( case
:INSTATE_DATE when to_date('00/00/0000','DD-MM-YYYY') then
return to_date('01/01/2010','DD-MM-YYYY');
else
return :INSTATE_DATE
end ) INSTATE_DATE
from table1
-
Originally Posted by aryasen
select
( case
:INSTATE_DATE when to_date('00/00/0000','DD-MM-YYYY') then
return to_date('01/01/2010','DD-MM-YYYY');
else
return :INSTATE_DATE
end ) INSTATE_DATE
from table1
You didn't test it, don't you?
look...
Code:
SQL>
SQL> select to_date('00/00/0000','DD-MM-YYYY') from dual;
select to_date('00/00/0000','DD-MM-YYYY') from dual
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I tried to_date also.. still its not working..
Im not sure whether im using to_date function properly.. please correct me if im wrong
INSTATE_DATE DATE "to_date (replace (replace (:REINSTATE_DATE, '/', ''), ' ', ''), 'mmddyyyy')"
-
Use decode() - look at second post.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks. Its working fine with boundfiller solution.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|