with temp as( select to_date('2010-12-31','yyyy-mm-dd') a from dual union all select to_date('2010-11-30','yyyy-mm-dd') a from dual union all select to_date('2011-1-31','yyyy-mm-dd') a from dual ) select add_months(a,-1) from temp 用add_months() 海量数据的话,建议先备份, 然后关闭redolog,删除索引,约束,触发器等, 更新后再重建
把年份和月份拼起来 用add_months()
--select * from DMS_SAFETYMONTHPLAN order by dms_safetymonthplan.monthplanidupdate DMS_SAFETYMONTHPLAN safemonth set safemonth.monthn=safemonth.monthn-1update DMS_SAFETYMONTHPLAN safemonth set safemonth.monthn=12,safemonth.yearn=safemonth.yearn-1 where safemonth.monthn=0
update t set t.year = to_char(add_months(to_date(year||month,'yyyymm'),-1),'yyyy') ,t.month= to_char(add_months(to_date(year||month,'yyyymm'),-1),mm)
update t set month = month - 1 ;update t set year = year - 1, month = 12 where month = 0 ;
update t set year = decode( month, 1, year - 1, year ), month = decode( month, 1, 12, month - 1 ) ;
WITH a AS( SELECT '2010' YEAR ,'01' MONTH FROM dual UNION ALL SELECT '2010' YEAR ,'10' MONTH FROM dual ) SELECT To_Char(Add_Months(To_Date(YEAR||MONTH,'yyyymm'),-1),'yyyymm') FROM a;
select to_date('2010-12-31','yyyy-mm-dd') a from dual
union all
select to_date('2010-11-30','yyyy-mm-dd') a from dual
union all
select to_date('2011-1-31','yyyy-mm-dd') a from dual
)
select add_months(a,-1) from temp 用add_months()
海量数据的话,建议先备份,
然后关闭redolog,删除索引,约束,触发器等,
更新后再重建
set t.year = to_char(add_months(to_date(year||month,'yyyymm'),-1),'yyyy')
,t.month= to_char(add_months(to_date(year||month,'yyyymm'),-1),mm)
set month = month - 1 ;update t
set year = year - 1,
month = 12
where month = 0 ;
set year = decode( month, 1, year - 1, year ),
month = decode( month, 1, 12, month - 1 )
;
update tb
set 年份=to_char(add_months(to_date(年份||月份,'yyyymm'),-1),'yyyy'),
月份=to_char(add_months(to_date(年份||月份,'yyyymm'),-1),'mm')
SQL> create table t_temp(years varchar2(10),months varchar2(10))
2 /表已创建。SQL> select * from t_temp;YEARS MONTHS
---------- ----------
2010 08
2011 09
2011 12
2010 12
2010 01
SQL> update t_temp set years=to_char(add_months(to_date(YEARS||MONTHS,'yyyymm'),-1),'yyyy'),
2 months=to_char(add_months(to_date(YEARS||MONTHS,'yyyymm'),-1),'mm')
3 /已更新5行。SQL> select * from t_temp
2 /YEARS MONTHS
---------- ----------
2010 07
2011 08
2011 11
2010 11
2009 12
SELECT '2010' YEAR ,'01' MONTH FROM dual
UNION ALL
SELECT '2010' YEAR ,'10' MONTH FROM dual
)
SELECT To_Char(Add_Months(To_Date(YEAR||MONTH,'yyyymm'),-1),'yyyymm') FROM a;