Milliseconds from DB2 TimeStamp

system · August 25, 2004, 9:55pm

Business Objects 6.1a and DB2

Trying to Select results from DB2 database by the Timestamp field is requiring the Date Time including Milliseconds.

How do I get the milliseconds into BO? (And out in the select statement), I’ve changed the db2.SBO to get HH.MM.SS, but what is the format for Milliseconds?

Thanks
Adam

Adam (BOB member since 2004-08-25)

system · August 25, 2004, 10:08pm

Try HH.MM.SS.MM or HH.MM.SS.mm. Not sure if this will work for DB2 we have a different d/b.

Michele Pinti (BOB member since 2002-06-17)

system · August 25, 2004, 10:52pm

Tried mm,ms,nnnnnn,fff,ffffff and none work with Business Objects for Formatting

I can fake it with 000000 , but then it won’t match up with data in DB2

Adam (BOB member since 2004-08-25)

system · August 26, 2004, 12:54pm

ya got me stumped; I ran thru the whole alphabet. I was in Designer on a known timestamp and was formatting it in object properties where you can see a custom format. Got as far as yyyy-mm-dd-hh:mm:ss. and nothing worked.
And this leads to a further question - if you build an object MICROSECOND(PS_LEDGER.LOAD_DTTM) which returns an integer, how do you format that? All I get is zeroes when I use number format.

scott copeland (BOB member since 2002-08-15)

system · September 7, 2004, 5:36pm

Solution for this problem:
Import DB2 TimeStamp as a 26 character String.

This bypasses the issue and the prompt sends back the entire string value.

Adam (BOB member since 2004-08-25)

system · January 14, 2005, 2:10am

Did you get any answer for that Milliseconds ?
As I am stuck with exactly the same issue with a SQL Server database.
I am just wondering if this is not the middleware the issue or may be a middleware configuration issue.

If you have a chance, just drop me an email : pbrenner@evolition.com.au

Cheers

Pascal

eVolition (BOB member since 2002-09-01)

system · February 3, 2005, 8:43am

hi. We 're using BO 6.1 ; DB2 V8.2
We have the same problem with the timestamp.
we couldn’t display the milliseconds in the report.
in oracle I can use to_char function to convert date to character.
Is there a smilar way to do it in DB2.
A solution in the designer will also be welcomed.
I couldn’t manage any of them.

fsengonul (BOB member since 2004-10-15)

system · February 3, 2005, 10:18am

DB2 doesn’t use the TO_CHAR() but just plain CHAR(). Check also http://www-106.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html for other tips around timestamps on DB2.

jobjoris (BOB member since 2004-06-30)