Chinaunix首页 | 论坛 | 博客
  • 博客访问: 103070744
  • 博文数量: 19283
  • 博客积分: 9968
  • 博客等级: 上将
  • 技术积分: 196062
  • 用 户 组: 普通用户
  • 注册时间: 2007-02-07 14:28
文章分类

全部博文(19283)

文章存档

2011年(1)

2009年(125)

2008年(19094)

2007年(63)

分类: Oracle

2008-03-31 21:59:06

来源:赛迪网    作者:Tim

“字符型”转换成“日期型”的实例脚本:

15:13:46 SQL> 
15:13:46 SQL> CREATE TABLE ycz_test_1
2 (row_id varchar2(100),
3 start_dt varchar2(100),
4 end_dt varchar2(100));

Table created

Executed in 0.02 seconds
15:13:47 SQL> CREATE TABLE ycz_test_2
2 (row_id varchar2(100),
3 start_dt date,
4 end_dt date);

Table created

Executed in 0.03 seconds
15:13:47 SQL> insert into ycz_test_1
2 values('1','','');

1 row inserted

Executed in 0.01 seconds
15:13:47 SQL> commit;

Commit complete

Executed in 0.01 seconds
15:13:47 SQL> insert into ycz_test_2
2 values
3 ('1',to_date('2007-1-1', 'yyyy-mm-dd'), to_date('2008-2-1', 'yyyy-mm-dd'));

1 row inserted

Executed in 0.01 seconds
15:13:47 SQL> commit;

Commit complete

Executed in 0.01 seconds
15:13:47 SQL> update ycz_test_1 a
2 set (a.start_dt,a.end_dt)=
3 (select b.start_dt,b.end_dt
4 from ycz_test_2 b
5 where a.row_id = b.row_id);

1 row updated

Executed in 0 seconds

15:13:47 SQL> commit;

Commit complete

Executed in 0.01 seconds

15:13:54 SQL> select * from ycz_test_1;

ROW_ID START_DT END_DT
------------------------
1 01-JUL-07 01-AUG-08

Executed in 0.03 seconds

15:14:02 SQL> 
15:18:07 SQL> 
15:18:08 SQL> update ycz_test_1 a
2 set (a.start_dt,a.end_dt)=
3 (select to_char(b.start_dt,'yyyy-mm-dd'),to_char(b.end_dt,'yyyy-mm-dd')
4 from ycz_test_2 b
5 where a.row_id = b.row_id);

1 row updated

Executed in 0.01 seconds

15:18:08 SQL> select * from ycz_test_1;

ROW_ID START_DT END_DT
--------------------------------------
 -------------------------------------
 -------------------------------------
1 2007-01-01 2008-02-01

Executed in 0.03 seconds
阅读(384) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~