topic Re: DB2 Time Conversion in QlikView

DB2 Time Conversion

noman212 — Thu, 13 Nov 2014 06:48:47 GMT

Hi friends,

i am using IBM FileNet DB2 it has a table which shows correct date but time is in this format '2014-11-13 06:05:01.077' which is incorrect how can i convert this time to real time ?? pls find the attached snap shot for further.

Re: DB2 Time Conversion

MK_QSL — Thu, 13 Nov 2014 07:24:16 GMT

Load

TImeStamp(Timestamp#(CHECKED_IN_DATE,'MMM DD YYYY h:mm:ss TT FFFFFF')) as CHECKEDINDATETimeStamp,

Date(Floor(Timestamp#(CHECKED_IN_DATE,'MMM DD YYYY h:mm:ss TT FFFFFF'))) as Date,

Time(Frac(Timestamp#(CHECKED_IN_DATE,'MMM DD YYYY h:mm:ss TT FFFFFF'))) as Time;

Load * FROM your DBDATABASE:

Re: DB2 Time Conversion

ashfaq_haseeb — Thu, 13 Nov 2014 07:24:28 GMT

Dear Noman,

Try like this

timestamp(timestamp#(YourDate,'YYYY-MM-DD hh.mm.ss.ffff'),'YYYY-MM-DD hh:mm:ss[.fff] TT') as Date,

DATE(YourDate,'YYYY-MM-DD hh:mm:ss[.fff] TT') as Date.

If you can post sample data it would be great.

Just post at least 5 lines of dtae.

Regards

ASHFAQ

Re: DB2 Time Conversion

its_anandrjs — Thu, 13 Nov 2014 07:54:33 GMT

Hi,

Try with

TimeStamp(Timestamp#(CHECKED_IN_DATE,'MMM DD YYYY h:mm:ss TT FFFFFF'),'MM/DD/YYYY h:mm:ss[.fff] TT') as

NewCHECKEDINDATE,

Time(Timestamp#(CHECKED_IN_DATE,'MMM DD YYYY h:mm:ss TT FFFFFF'),''h:mm:ss TT'') as

NewCHECKEDINDATETime,

Regards

Anand

Re: DB2 Time Conversion

Fri, 18 Sep 2015 14:14:32 GMT

how are you connecting to FileNet database