updating date value

Database

updating date value


Hi,

I have a date column with values for all the months in the year 2007. Each month has about 1000 records, likewise its for all twelve months. I want to udpate the year field for all the records in 2007 march to 2008 march. e.g all records having 2007-03-* to be changed to 2008-03-*. Any idea hw i can go about it????

Thnx in advnce,

Rock
5 REPLIES

Re: updating date value

Hi,

I think by using ADD_MONTHS you can get the expected result…

Eg:- select ADD_MONTHS(date_column, 12) from table_name;

Regards,
Balamurugan
N/A

Re: updating date value

Hi Rock,

update tab
set col = add_months(col, 12)
where col between date '2007-03-01' and date '2007-03-31'

or
where extract(year from col) = 2007 and extract(month from col) = 3

or
where col / 100 = 10703

The first one is the best for a potential index/partition access.
The last one is the shortest to write but the hardest to understand :-)

Dieter

Re: updating date value


Hi Dieter,

Thanx for the update sql's. As u said the last one was bit difficult to understand :)

regards,

rock

Re: updating date value


hi,

Thanks for the sql.

regards,

rock

Re: updating date value

Rock,

Have you had an opportunity to check the SYSCALENDAR database?

There, you will find a table called calendar that is already populated for many years and it is very useful, in my opinion, to help you to deal with dates.

I hope this helps!