How to update DATE data. 4

[fiat2]

Using Oracle 9.
I've got some data in a DATE field and right now it looks something like this:
10/18/0008

I would like to update all the records to take the records current Month, Day and update the year to 2008.
As it is, all the data that I need to update is from 2008, if that helps.

thanks.

 

[Turkbear]

Hi,
Try something like:

Update table_name set 
Date_field = 
to_date(to_text(Date-Field,'MM')||'/'||to_text(Date-Field),'DD')||'/'||'2008','MM/DD/YYYY');

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LKBrwnDBA]

-- or --

Update table_name 
   set Date_field = TRUNC(SYSDATE);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Oooooh, now I understand, you want to KEEP the column's day/month and just change the year, right?

OK, do tuckbear's code using the TO_CHAR() function:

Update Table_Name
   Set Date_Field = To_Date(To_Char(Date_Field,'MM')||To_Char(Date_Field),'DD')||'2008','MMDDYYYY');

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Or perhaps a little simpler:

Update Table_Name
   Set Date_Field = To_Date(To_Char(Date_Field,'MMDD')||'2008','MMDDYYYY');

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
As usual Santa, short and sweet.....Here's a star.

( Working with different systems makes remembering syntax difficult..hence, my to_text instead of to_char goof )



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[fiat2]

Wow! great little thread here.

Thanks to all for your valuable input!!!

 

[sem]

One more soluion:

Update Table_Name
Set Date_Field = trunc(sysdate,'Y')+ (Date_Field-trunc(Date_Field,'Y'));

Contrary to other solutions it preserves time part of the field. I also suppose that it may be faster but just a little bit.

Regards, Dima

http://seminihin.narod.ru