DB2中字符、数字和日期类型之间的转换
标签（空格分隔）： DB2 SQL
一般我们在使用DB2或Oracle的过程中,经常会在数值<->字符<->日期三种类型之间做转换,那么在DB2和Oracle中,他们分别是如何实现的呢?在Oracle这几个类型之间的转换是十分方便的，通过to_char|to_date|to_number函数即可完成类型转换。本小节主要介绍DB2中的一些知识，从Oracle转过来的DBA或开发人员，可以对比着学习。
字符型到日期时间型的互换
| 字符型 | 日期时间型 | 转换函数 |
|---|---|---|
| char | date | date(col_a) |
| char | time | time(col_a) |
| char | timestamp | timestamp(col_a) 或者 to_date(--string-expression,format-string) |
注：to_date实际是TIMESTAMP_FORMAT函数的一个同义词
| 日期时间型 | 字符型 | 转换 |
|---|---|---|
| date | char | char(col_a) |
| time | char | char(col_a) |
| timestamp | char | char(col_a) 或 to_char(--timestamp-expression--,format-string--) |
注：to_char实际是VARCHAR_FORMAT函数的一个同义词
字符型到数值型的转换
| 字符型 | 数值型 | 转换函数 |
|---|---|---|
| char | smallint | Integer(col_a) |
| char | integer | Integer(col_a) |
| char | bigint | cast(col_a as bigint) |
| char | decimal | cast(col_a as decimal(8,2)) |
| char | double | cast(cast(char(cast(col_a as decimal(8,2))) as decimal(8,2)) as double) |
数值型到字符型的转换
| 数值型 | 字符型 | 转换函数 |
|---|---|---|
| smallint | char | char(col_a) |
| integer | char | char(col_a) |
| bigint | char | char(col_a) |
| double | char | char(cast(col_a AS decimal(8,2))) |
| decimal(8,2) | char | Digits(col_a) |
测试一下：
字符型和日期时间型的互换
测试1 "Human Readable Format" -> "DB2 Interal Format"
describe
select date('2009-09-01 '),
date('09/27/2009 '),
time('12:23:34 '),
timestamp('2009-02-26-14.28.40.234000 ')
from sysibm.dual;
select date('2009-09-01 '),
date('09/27/2009 '),
time('12:23:34 '),
timestamp('2009-02-26-14.28.40.234000 ')
from sysibm.dual;
结果1
describe
select date('2009-09-01 '),
date('09/27/2009 '),
time('12:23:34 '),
timestamp('2009-02-26-14.28.40.234000 ')
from sysibm.dual;
completed successfully.
列信息
列数：4
SQL 类型 类型长度 列名 名称长度
-------------------- ----------- ------------------------------ -----------
384 DATE 10 1 1
384 DATE 10 2 1
388 TIME 8 3 1
392 TIMESTAMP 26 4 1
Statement processed successfully in 0.08 secs.
select date('2009-09-01 '),
date('09/27/2009 '),
time('12:23:34 '),
timestamp('2009-02-26-14.28.40.234000 ')
from sysibm.dual;
completed successfully.
1 row selected in 0.01 secs.
1 2 3 4
---------- ---------- -------- -------------------
2009-09-01 2009-09-27 12:23:34 2009-02-26 14:28:40
测试2
describe
select to_date('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss'),
timestamp_format('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss'),
timestamp_format('2009-02-26 ','yyyymmdd'),
timestamp_format('14.28.40 ','hh24:mi:ss')
from sysibm.dual;
select to_date('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss'),
timestamp_format('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss'),
timestamp_format('2009-02-26 ','yyyymmdd'),
timestamp_format('14.28.40 ','hh24:mi:ss')
from sysibm.dual;
结果2
describe
select to_date('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss'),
timestamp_format('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss'),
timestamp_format('2009-02-26 ','yyyymmdd'),
timestamp_format('14.28.40 ','hh24:mi:ss')
from sysibm.dual;
completed successfully.
列信息
列数：4
SQL 类型 类型长度 列名 名称长度
-------------------- ----------- ------------------------------ -----------
392 TIMESTAMP 26 1 1
392 TIMESTAMP 26 2 1
392 TIMESTAMP 26 3 1
392 TIMESTAMP 26 4 1
Statement processed successfully in 0.07 secs.
select to_date('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss'),
timestamp_format('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss'),
timestamp_format('2009-02-26 ','yyyymmdd'),
timestamp_format('14.28.40 ','hh24:mi:ss')
from sysibm.dual;
completed successfully.
1 row selected in 0.01 secs.
1 2 3 4
------------------- ------------------- ------------------- -------------------
2009-01-01 12:23:45 2009-01-01 12:23:45 2009-02-26 00:00:00 2017-01-01 14:28:40
测试3
describe
select cast(timestamp_format('2009.02.26 ','yyyymmdd') as date),
cast(timestamp_format('20090226 ','yyyymmdd') as date),
cast(timestamp_format('2009/02/26 ','yyyymmdd') as date)
from sysibm.dual;
select cast(timestamp_format('2009.02.26 ','yyyymmdd') as date),
cast(timestamp_format('20090226 ','yyyymmdd') as date),
cast(timestamp_format('2009/02/26 ','yyyymmdd') as date)
from sysibm.dual;
结果3
describe
select cast(timestamp_format('2009.02.26 ','yyyymmdd') as date),
cast(timestamp_format('20090226 ','yyyymmdd') as date),
cast(timestamp_format('2009/02/26 ','yyyymmdd') as date)
from sysibm.dual;
completed successfully.
列信息
列数：3
SQL 类型 类型长度 列名 名称长度
-------------------- ----------- ------------------------------ -----------
384 DATE 10 1 1
384 DATE 10 2 1
384 DATE 10 3 1
Statement processed successfully in 0.06 secs.
select cast(timestamp_format('2009.02.26 ','yyyymmdd') as date),
cast(timestamp_format('20090226 ','yyyymmdd') as date),
cast(timestamp_format('2009/02/26 ','yyyymmdd') as date)
from sysibm.dual;
completed successfully.
1 row selected in 0.01 secs.
1 2 3
---------- ---------- ----------
2009-02-26 2009-02-26 2009-02-26
测试4
describe
select char(current date),
char(current time),
char(current timestamp),
to_char(current timestamp,'yyyy-mm-dd hh24:mi:ss'),
to_date('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss')
from sysibm.dual;
select char(current date),
char(current time),
char(current timestamp),
to_char(current timestamp,'yyyy-mm-dd hh24:mi:ss'),
to_date('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss')
from sysibm.dual;
结果4
describe
select char(current date),
char(current time),
char(current timestamp),
to_char(current timestamp,'yyyy-mm-dd hh24:mi:ss'),
to_date('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss')
from sysibm.dual;
completed successfully.
列信息
列数：5
SQL 类型 类型长度 列名 名称长度
-------------------- ----------- ------------------------------ -----------
452 CHARACTER 10 1 1
452 CHARACTER 8 2 1
452 CHARACTER 26 3 1
448 VARCHAR 254 4 1
392 TIMESTAMP 26 5 1
Statement processed successfully in 0.07 secs.
select char(current date),
char(current time),
char(current timestamp),
to_char(current timestamp,'yyyy-mm-dd hh24:mi:ss'),
to_date('2009-01-01 12:23:45','yyyy-mm-dd hh24:mi:ss')
from sysibm.dual;
completed successfully.
1 row selected in 0.01 secs.
1 2 3 4 5
---------- -------- -------------------------- ------------------- -------------------
2017-01-30 22.20.25 2017-01-30-22.20.25.938000 2017-01-30 22:20:25 2009-01-01 12:23:45
字符型到数值型的转换
测试5
drop table TEST_DATATYPE;
create table TEST_DATATYPE
(
col_a char(2),
col_b char(4),
col_c char(10),
col_d char(10),
col_e char(10)
);
insert into TEST_DATATYPE values('1','200','30000','4000.04','5000000');
describe
select * from TEST_DATATYPE;
describe
select Integer(col_a),
Integer(col_b),
cast(col_c as bigint),
cast(col_d as decimal(8,2)),
cast(cast(char(cast(col_e as decimal(12,2))) as decimal(12,2)) as double)
from TEST_DATATYPE;
结果5
drop table TEST_DATATYPE;
completed successfully.
Statement processed successfully in 0.11 secs.
create table TEST_DATATYPE
(
col_a char(2),
col_b char(4),
col_c char(10),
col_d char(10),
col_e char(10)
);
completed successfully.
Statement processed successfully in 0.01 secs.
insert into TEST_DATATYPE values('1','200','30000','4000.04','5000000');
completed successfully.
1 row inserted in 0.00 secs.
describe
select * from TEST_DATATYPE;
completed successfully.
列信息
列数：5
SQL 类型 类型长度 列名 名称长度
-------------------- ----------- ------------------------------ -----------
453 CHARACTER 2 COL_A 5
453 CHARACTER 4 COL_B 5
453 CHARACTER 10 COL_C 5
453 CHARACTER 10 COL_D 5
453 CHARACTER 10 COL_E 5
Statement processed successfully in 0.08 secs.
describe
select Integer(col_a),
Integer(col_b),
cast(col_c as bigint),
cast(col_d as decimal(8,2)),
cast(cast(char(cast(col_e as decimal(12,2))) as decimal(12,2)) as double)
from TEST_DATATYPE;
completed successfully.
列信息
列数：5
SQL 类型 类型长度 列名 名称长度
-------------------- ----------- ------------------------------ -----------
497 INTEGER 4 1 1
497 INTEGER 4 2 1
493 BIGINT 8 3 1
485 DECIMAL 8, 2 4 1
481 DOUBLE 8 5 1
Statement processed successfully in 0.08 secs.
数值型到字符型的转换
测试6
drop table TEST_DATATYPE;
create table TEST_DATATYPE
(
col_a smallint,
col_b integer,
col_c bigint,
col_d decimal(8,2),
col_e double
);
insert into TEST_DATATYPE values(1,20,3000,4000.44,55555);
describe
select * from TEST_DATATYPE;
describe
select char(col_a),
char(col_b),
char(col_c),
digits(col_d),
cast(col_d as decimal(8,2)),
char(cast(col_e as decimal(8,2)))
from TEST_DATATYPE;
select char(col_a)||','||char(col_b)||','||char(col_c)||','||digits(col_d)||','||char(cast(col_e as decimal(8,2)))
from TEST_DATATYPE;
结果6
drop table TEST_DATATYPE;
completed successfully.
Statement processed successfully in 0.00 secs.
create table TEST_DATATYPE
(
col_a smallint,
col_b integer,
col_c bigint,
col_d decimal(8,2),
col_e double
);
completed successfully.
Statement processed successfully in 0.01 secs.
insert into TEST_DATATYPE values(1,20,3000,4000.44,55555);
completed successfully.
1 row inserted in 0.00 secs.
describe
select * from TEST_DATATYPE;
completed successfully.
列信息
列数：5
SQL 类型 类型长度 列名 名称长度
-------------------- ----------- ------------------------------ -----------
501 SMALLINT 2 COL_A 5
497 INTEGER 4 COL_B 5
493 BIGINT 8 COL_C 5
485 DECIMAL 8, 2 COL_D 5
481 DOUBLE 8 COL_E 5
Statement processed successfully in 0.07 secs.
describe
select char(col_a),
char(col_b),
char(col_c),
digits(col_d),
cast(col_d as decimal(8,2)),
char(cast(col_e as decimal(8,2)))
from TEST_DATATYPE;
completed successfully.
列信息
列数：6
SQL 类型 类型长度 列名 名称长度
-------------------- ----------- ------------------------------ -----------
453 CHARACTER 6 1 1
453 CHARACTER 11 2 1
453 CHARACTER 20 3 1
453 CHARACTER 8 4 1
485 DECIMAL 8, 2 COL_D 5
453 CHARACTER 10 6 1
Statement processed successfully in 0.08 secs.
select char(col_a)||','||char(col_b)||','||char(col_c)||','||digits(col_d)||','||char(cast(col_e as decimal(8,2)))
from TEST_DATATYPE;
completed successfully.
1 row selected in 0.02 secs.
1
-----------------------------------------------------------
1 ,20 ,3000 ,00400044,55555.00
