Home » SQL & PL/SQL » SQL & PL/SQL » Update Year portion in date column
Update Year portion in date column [message #344423] Fri, 29 August 2008 14:51 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Window Professional 2002 and Release 10.2.0.3.0

HI ..

I am inserting some data to table2 from table1.. The date field of table1 containig some invalid date values like ..

11/30/0200 .... 3/24/0005


I need to add the logic in the insert statement, so that if it finds invalid year in any date field, update Year portion of that value to 2000.

11/30/0200 should change to 11/30/2000

3/24/0005  should change to 3/24/2000..



I have tried to write the logic.. Please give me small hint to complete this..


insert into table2 select
col1, col2,col2,
case
when to_char(datefield,'YYYY') < 1900 then "________"
else datefield
end
as datefield
from table1


Appreciate your time and help....
Re: Update Year portion in date column [message #344427 is a reply to message #344423] Fri, 29 August 2008 15:30 Go to previous message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

insert into table2 select
col1, col2,col2,
case
when to_char(datefield,'YYYY') < 1900 then to_date(substr(to_char(datefield,'MM/DD/YYYY'),1,6)||1900,'MM/DD/YYYY')
else datefield
end
as datefield
from table1


I just found the soloution of this problem..
Previous Topic: check constraint in the same table (merged 3)
Next Topic: Function
Goto Forum:
  


Current Time: Tue Dec 06 10:18:54 CST 2016

Total time taken to generate the page: 0.11756 seconds