Conversions between characters, numbers, and date types in DB2

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