@lumincinta 2017-01-30T14:41:28.000000Z 字数 8569 阅读 369

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

DB2中字符、数字和日期类型之间的转换

字符型到日期时间型的互换

字符型到数值型的转换

数值型到字符型的转换

测试一下：

字符型和日期时间型的互换

字符型到数值型的转换

数值型到字符型的转换

内容目录