Conversions between characters, numbers, and date types in DB2
generally we use DB2 or Oracle in the process, often in the number <-> character <-> date three types of conversion between, then in DB2 and Oracle, how they were implemented? The conversion between these types of Oracle is very convenient, and the type conversion can be done through the To_char|to_date|to_number function. This section focuses on some of the knowledge in DB2, which can be compared to DBAs or developers who have been transferred from Oracle.
conversion of digital to character type
The Col_a field in DB2 |
Field type |
Conversion to character type |
smallint |
char (COL_A) |
Integer |
bigint |
Double |
char (CAST (col_a as Decimal (8,2))) |
Decimal (8,2) |
Digits (COL_A) |
Test it:
Sql>CREATE TABLE Test_datatypeSql>(Sql>col_a smallint,Sql>col_b Integer,Sql>Col_c bigint,Sql>col_d Decimal (8,2),Sql>Col_e DoubleSql>);Db20000iSQLCommand completed successfully. Sql>INSERT into Test_datatype values (1,20,3000,4000.44,55555);Db20000i SQL command completed successfully. Sql>describe select * from Test_datatype
Column Information column number: 5 SQL Type type length column name length--------------------------------------------- ---------------------------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 5sql>select * from Test_datatype;Col_a col_b Col_c col_d col_e-------------------------------------------------------------- ---------1 4000.44 +5.55550000000000E+004 1 records selected. Sql>Select char (col_a), char (Col_b), char (Col_c), digits (col_d), char (CAST (col_e as Decimal (8,2))) from Test_datatype;1 2 3 4 5-------------------------------------------------------1 20 3000 00400044 055555.00 1 records have been selected. Sql>Select char (col_a) | | ', ' | | char (col_b) | | | ', ' | | char (Col_c) | | | ', ' | | Digits (col_d) | | ', ' | | char (CAST (col_e as Decimal (8,2))) from Test_datatype;1-----------------------------------------------------------1, 20, 3000, 00400044,055555.00 1 Record is selected. Sql>
character-to-digital conversions
The Col_a field in DB2 |
Field type |
Number Type |
Transformation |
Char |
smallint |
Integer (Trim (char (col_a))) |
Integer |
bigint |
Cast (char (col_a) as bigint) |
Decimal |
Cast (col_a as Decimal (8,2)) |
Double |
Cast (CAST (char (a.col_a as Decimal (8,2)) as decimal (8,2) as Double) |
Test it.
Sql>CREATE TABLE Test_datatypeSql>(Sql>col_a char (2),Sql>Col_b char (4),Sql>Col_c Char (TEN),Sql>Col_d Char (TEN),Sql>Col_e Char (TEN)Sql>);Db20000i SQL command completed successfully. Sql>INSERT into Test_datatype values (' 1 ', ' $ ', ' 30000 ', ' 4000.04 ', ' 5000000 ');Db20000i SQL command completed successfully. Sql>describe select * from Test_datatype;Column Information column number: 5 SQL Type type length column name length--------------------------------------------- ---------------------------453 CHARACTER 2 col_a 5 453 Charact ER 4 col_b 5 453 CHARACTER Col_c 5 453 CHARACTER col_d 5 453 CHARACTER Ten Col_e 5sql>Select Integer (Trim (char (col_a))),Sql>Integer (Trim (char (col_b))),Sql>cast (char (Col_c) as bigint),Sql>cast (col_d as Decimal (8,2)),Sql>Cast (CAST (char (col_e as Decimal (12,2)) as decimal (12,2) as Double)Sql>From Test_datatype;1 2 3 4 5------------------------------------------------------------- ---------------1 30000 4000.04 +5.00000000000000E+006 1 records selected. Sql>
character-to-date-time-type interchange
The Col_a field in DB2 |
Field type |
Date-Time Type |
Char |
Date (Trim (char (col_a))) Timestamp (Trim (char (col_a))) or To_date (--string-expression,format-string (DB2 9) |
Note: to_date is actually a synonym for the Timestamp_format function. |
Field type |
Character class |
Date |
char (COL_A) |
Time |
Timestamp |
char (col_a) or To_char (--timestamp-expression--, format-string--) (DB2 9) |
Note: To_char is actually a synonym for the Varchar_format function. |
Let's test it.
Sql>Select Date (Trim (char (' 2009-09-01 '))),Sql>Time (Trim (char (' 12:23:34 '))),Sql>Timestamp (Trim (char (' 2009-02-26-14.28.40.234000 '))) from Sysibm.dual;1 2 3--------------------------------------------2009-09-01 12:23:34 2009-02-26-14.28.40.234000 1 Records selected Choose. Sql>Select char (current date), char (current time), char (current timestamp) from Sysibm.dual;1 2 3--------------------------------------------2009-02-26 14:28:40 2009-02-26-14.28.40.234000 1 Records selected Choose. Sql>Select To_char (current timestamp, ' Yyyy-mm-dd hh24:mi:ss ') from Sysibm.dual;--DB2 9 added 1--------------------------2009-02-26 14:45:531 record selected. Sql>Select To_date (' 2009-01-01 12:23:45 ', ' yyyy-mm-dd hh24:mi:ss ') from Sysibm.dual;--DB2 1--------------------------added in version 9 2009-01-01-12.23.45.0000001 records have been selected. Sql>
Conversions between characters, numbers, and date types in DB2