关于字符型转换成日期型的问题

最新推荐文章于 2022-12-27 08:47:07 发布

转载最新推荐文章于 2022-12-27 08:47:07 发布 · 441 阅读

本文演示了在Oracle数据库中创建包含不同数据类型的表格，并通过SQL语句进行数据插入、更新及格式转换的过程。具体包括使用VARCHAR2类型与DATE类型的数据表创建、数据插入与提交操作，以及通过UPDATE语句实现不同数据类型间的转换。

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-7-1', 'yyyy-mm-dd'), to_date('2008-8-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-07-01 2008-08-01

Executed in 0.03 seconds
[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9460545/viewspace-927250/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9460545/viewspace-927250/