Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Post Unanswered: DB2, editing Timestamp(char) to (integer)

    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.
    Last edited by Yetunde; 11-05-09 at 07:48.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    try to remove the characters (: . )with translate and convert to integer
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    I have tried all possible best to remove the character to integer but it seems not working, It gives no room for edition.

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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)... )
    Last edited by MarkhamDBA; 11-13-09 at 12:31.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Decimal(18,0)

    Quote Originally Posted by MarkhamDBA View Post
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yetunde,
    what format do you want from timestamp 'yyyy-mm-dd-hh.mi.ss.ssssss'?

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Lenny77 View Post
    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
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Post decimal(..., 23) ...instruction to use...

    Quote Originally Posted by MarkhamDBA View Post
    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

  9. #9
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Lenny77 View Post
    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
    Last edited by MarkhamDBA; 11-13-09 at 14:39.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Like integer

    Quote Originally Posted by MarkhamDBA View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •