第一张表如下
create table Report
(
id number primary key not null,
name varchar2(180) not null,
orgId int not null,
year int not null,
month int not null,
);我想在表中添加两个字段 startDate date和endDate date,都可以为null的,这样前面的数据里这两个字段肯定为空了,我想通过表里的year和month的得到两个日期,然后更新到新的数据表中,并且startDate为本条记录的Year年month月的第一天,而endDate为Year年month+1月的第一天。高手们快点帮帮吗吧!在线等呢
create table Report
(
id number primary key not null,
name varchar2(180) not null,
orgId int not null,
year int not null,
month int not null,
);我想在表中添加两个字段 startDate date和endDate date,都可以为null的,这样前面的数据里这两个字段肯定为空了,我想通过表里的year和month的得到两个日期,然后更新到新的数据表中,并且startDate为本条记录的Year年month月的第一天,而endDate为Year年month+1月的第一天。高手们快点帮帮吗吧!在线等呢
set startDate = to_date(to_char(year)||to_char(month,'09'),'YYYYMM'),
endDate = to_date(to_char(year)||to_char(month+1,'09'),'YYYYMM');
alter table report add endDate date;update report
set startDate=to_date(to_char(year)||'-'||to_char(month)||'-'||'1','yyyy-mm-dd'),
endDate=to_date(to_char(year)||'-'||to_char(month+1)||'-'||'1','yyyy-mm-dd');
(
id number primary key not null,
name varchar2(180) not null,
orgId int not null,
year int not null,
month int not null
); insert into report values(10000,'zhansan',000,2008,9);
insert into report values(10001,'lisi',001,2007,12);
insert into report values(10002,'wangwu',002,2009,2);
insert into report values(10003,'sunliu',003,2007,5);
SQL> alter table report add (startDate date,endDate date) ;表已更改。SQL> select * from report; ID NAME ORGID YEAR MONTH STARTDATE ENDDATE
---------- -------------------- ---------- ---------- ---------- -------------- --------------
10000 zhansan 100 2008 9
10001 lisi 101 2007 12
10002 wangwu 102 2009 2
10003 sunliu 103 2007 5
SQL> select * from report; ID NAME ORGID YEAR MONTH STARTDATE ENDDATE
---------- -------------------- ---------- ---------- ---------- -------------- --------------
10000 zhansan 100 2008 9
10001 lisi 101 2007 12
10002 wangwu 102 2009 2
10003 sunliu 103 2007 5SQL> merge into report a
2 using (select id,to_date(lpad(year,4,0)||lpad(month,2,0)||'01','yyyy-mm-dd') sd from report) b
3 on (a.id=b.id)
4 when matched then
5 update set startDate=b.sd,endDate=last_day(b.sd)+1;4 行已合并。SQL>
SQL> select *from report; ID NAME ORGID YEAR MONTH STARTDATE ENDDATE
---------- -------------------- ---------- ---------- ---------- -------------- --------------
10000 zhansan 100 2008 9 01-9月 -08 01-10月-08
10001 lisi 101 2007 12 01-12月-07 01-1月 -08
10002 wangwu 102 2009 2 01-2月 -09 01-3月 -09
10003 sunliu 103 2007 5 01-5月 -07 01-6月 -07
endDate=add_months(to_date(year||'-'||month||'-01','yyyy-mm-dd'),1)
select a.*,to_date(year||'-'||month||'-01','yyyy-mm-dd') as startDate,
add_months(to_date(year||'-'||month||'-01','yyyy-mm-dd'),1) as endDate
from report a;