All Forums Database
rohitbeth 32 posts Joined 10/07
15 Jan 2008
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

Balamurugan B 81 posts Joined 09/07
16 Jan 2008

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

Regards,
Balamurugan

dnoeth 4628 posts Joined 11/04
16 Jan 2008

Hi Rock,update tabset 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) = 3orwhere col / 100 = 10703The 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

Dieter

rohitbeth 32 posts Joined 10/07
22 Jan 2008

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

rohitbeth 32 posts Joined 10/07
22 Jan 2008

hi,Thanks for the sql.regards,rock

famalau 43 posts Joined 08/07
24 Jan 2008

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!

Regards,

Fabio

You must sign in to leave a comment.