DB2, editing Timestamp(char) to (string)

Yetunde · #1 (**permalink**) 11-05-09, 06:35

Hi,

I am currently working with DB2 9.1, but my timestamps is in char after running my query, this makes it difficult to interpret this report and I try editing the coloum which I found difficult as well.

I am working on Window server 2003 EE, and I am trying to generate report for ITM using BIRT Bussiness Intelligent reporting tools v2.2.2, my database connection with BIRT was successful but the date of the report is an issue to explain.

Please help me.

Thank u.

przytula_guy · #2 (**permalink**) 11-05-09, 07:15

try to remove the characters (: . )with translate and convert to integer

Yetunde · #3 (**permalink**) 11-13-09, 11:15

I have tried all possible best to remove the character to integer but it seems not working, It gives no room for edition.

MarkhamDBA · #4 (**permalink**) 11-13-09, 11:27

first that comes to mind is:

c1 with value let's say '2002-12-25-17.12.30.123456'

integer(substr(c1,1,4)||substr(c1,6,2)||substr(c1, 9,2)||substr(c1,12,2)... )

Lenny77 · #5 (**permalink**) 11-13-09, 11:51

Quote:

Originally Posted by MarkhamDBA

first that comes to mind is:

c1 with value let's say '2002-12-25-17.12.30.123456'

integer(substr(c1,1,4)||substr(c1,6,2)||substr(c1, 9,2)||substr(c1,12,2)... )

Integers have a limit +2147483647.... After this you will get arithmetic exception error.

Better, instead of integer, using DECIMAL(18,0)....

Lenny

tonkuma · #6 (**permalink**) 11-13-09, 12:05

Yetunde,
what format do you want from timestamp 'yyyy-mm-dd-hh.mi.ss.ssssss'?

MarkhamDBA · #7 (**permalink**) 11-13-09, 12:22

Quote:

Originally Posted by Lenny77

Integers have a limit +2147483647.... After this you will get arithmetic exception error.

Better, instead of integer, using DECIMAL(18,0)....

Lenny

you are right. actually DECIMAL function can be used to convert timestamp to decimal data type. Excerpt from DB2 SQL documentation:

function with arguments = DECIMAL(2000-03-21-12.02.21.123456, 23)
precision and scale = (23, 6)
result = 20000321120221.123456

Lenny77 · #8 (**permalink**) 11-13-09, 12:34

Quote:

Originally Posted by MarkhamDBA

you are right. actually DECIMAL function can be used to convert timestamp to decimal data type. Excerpt from DB2 SQL documentation:

function with arguments = DECIMAL(2000-03-21-12.02.21.123456, 23)
precision and scale = (23, 6)
result = 20000321120221.123456

Actually:

Code:

select decimal(replace(
         translate(varchar(current timestamp), ' ', '-.'), ' ', ''), 23)
from sysibm.sysdummy1

Lenny

MarkhamDBA · #9 (**permalink**) 11-13-09, 13:30

Quote:

Originally Posted by Lenny77

Actually:

Code:

select decimal(replace(
         translate(varchar(current timestamp), ' ', '-.'), ' ', ''), 23)
from sysibm.sysdummy1

Lenny

Lenny, why do you need replace/translate/varchar here? argument of DECIMAL function can be of timestamp type. I tested it and it works

Lenny77 · #10 (**permalink**) 11-13-09, 14:24

Quote:

Originally Posted by MarkhamDBA

Lenny, why do you need replace/translate/varchar here? argument of DECIMAL function can be of timestamp type. I tested it and it works

Because I don't want to have a fraction....

Code:

select current timestamp, 
decimal(replace(translate(
           varchar(current timestamp), ' ', '-.'), ' ', ''), 23)
from sysibm.sysdummy1

Now you can operate with result in same way how you are working with integers....

Code:

select 
decimal(replace(translate(
           varchar(current timestamp), ' ', '-.'), ' ', ''), 23)
-
decimal(replace(translate(
            varchar(current timestamp - 111 minute), ' ', '-.'), ' ', ''), 23)
from sysibm.sysdummy1

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech