I am trying to change a field from varchar2 to date datatype. However, I keep getting an error that the column must be empty. The table has over 10,000 records.
sample of some data:
01/Mar/1996
01/Feb/1996
01/Jan/1996
Here is the workaround I have been trying.
alter table ntiac_table add
(publication_date_copy date) ;
update ntiac_table
set publication_date_copy=publ
alter table ntiac_table set unused column
publication_date;
alter table ntiac_table drop unused columns;
alter table ntiac_table rename column
publication_date_copy TO publication_date;
I thought this would work because it worked when I changed a datatype of another field from varchar2 to number. However, when I run the above code I get an error after the following code is run:
update ntiac_table
set publication_date_copy=publ
ORA-01841: (full) year must be between -4713 and +999, and not be 0
Could someone please provide a solution
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I tried it and I got the same error. I tried querying the field to find out if there were any fields with non valid value. But no rows were returned. It is possible that some of the values aren't valid. I can't seem to find it. I tried
select pdf_name, publication_date from ntiac_table where publication_date not like '__/___/____';
See if this will get you where you need to be. It uses Cybers method of a temp table:
--------------------------
drop table tab1;
create table tab1 (
col1 char(1),
col2 varchar2(20)
);
insert into tab1 values('a','01/Mar/1996');
insert into tab1 values('b','01/Feb/1996');
insert into tab1 values('c','fred');
insert into tab1 values('d','01/Jan/1996');
insert into tab1 values('e','barney');
commit;
declare
junk date;
begin
for i in (select * from tab1) loop
begin
junk := to_date(i.col2,'DD/MON/YYY
exception when others then
dbms_output.put_line('Bad data: ' || i.col1);
end;
end loop;
end;
/
--clean up bad data
delete from tab1 where col1 in ('c','e');
commit;
drop table junk;
create table junk as (select col1, to_date(col2,'DD/MON/YYYY'
update tab1 set col2 = null;
alter table tab1 modify col2 date;
update tab1 set col2 = (select new_col2 from junk where junk.col1=tab1.col1);
commit;
select * from tab1;
There are basically two obstacles you have to get past:
1. Oracle will not allow you to change the data type of a column that has any data in it.
2. Whenever varchar2 columns are used to store "date" information, they almost always get some records with invalid date values, or some values that have a different date format than the rest.
So, you have to make a place to put the existing column values for a while (this can either be another column in this table, or a "temporary" table that contains the primary key value, plus the existing varchar2/date value). Then you have to find and correct the invalid date values. The you can alter the current column, and load the valid data back into it with a to_date operator.
I don't think that's the first problem you need to solve. You first have to find and correct the records with invalid date values. Only after you do that, can you try to load them into a date column.
You need to use a PL\SQL function along with a query that checks every record in the table to find the invalid dates first. Here is a function you can use for that:
create or replace function bad_date (char_val in varchar2, format_mask in varchar2) return number as
dummy date;
begin
if not (char_val is null) then
dummy := to_date(char_val,format_ma
end if;
return 0;
exception
when others then
return 1;
end;
/
Then use that in a query like this:
select rowid, b.publication_date
from ntiac_table b
where (bad_date,b.publication_da
That will identify the records you need to fix manually first. After that, you can do your update like this:
update ntiac_table
set publication_date_copy = to_date(publication_date'D
yea thats true markgeer ,
first u need to find out the invalid date formatted records
sikyala , just modify the sql of markgeer to
select rowid, b.publication_date
from ntiac_table b
where bad_date(b.publication_dat
or you can update the column like following
if you want to put null date instead of invalid dates
update ntiac_table
set publication_date_copy = decode(bad_date(b.publicat
if you want to put current date instead of invalid dates
update ntiac_table
set publication_date_copy = decode(bad_date(b.publicat
for all the above said, bad_date function should be there compiled.
Sorry that I am just now responding. Monday was a holiday and the past 2 days I have been in meetings all day and away from my office.
Honestly, when I didn't receive anymore input after I submitted a comment on 9/1/05 I figured out how to find the bad dates and correct them before I got the response dated 9/5/05.
What I did is I created a table in a new database and assigned that field the Date datatype. Then I loaded the data into that table using sqlldr. What it did was reject or not load all the records with bad dates. I looked in the bad data text file and was able to identify several records where the date was 01 Jan 0000. So I corrected the date in the database where I have been working on the data.
Then I ran SDutta code: update ntiac_table
set publication_date_copy=to_d
and it worked fine.
Your soultion was one of the many ways to solve this problem. SDutta provided the final update after you had clean data. Both markgeer and myself provided solutions to locate the bad data. Cyber-X-Cross also gave a probable solution. All prior to 9-1-05.
Please close out as you see fit. Might I suggest a 4-way split?
Business Accounts
Answer for Membership
by: SDuttaPosted on 2005-08-31 at 10:21:22ID: 14795785
You should use :
update ntiac_table
set publication_date_copy=to_d