topic Re: DB2 Conversion in QlikView

DB2 Conversion

noman212 — Fri, 21 Nov 2014 05:25:06 GMT

Hi friend,

I am stuck in a problem need ur help to resolve this

i have a table want to convert the timestamp of feild from integer to datetime.

LOGQVD:

LOAD "F_WOBNUM",

"F_WORKSPACEID",

"F_OPERATIONID",

"F_INSTRSHEETID",

"F_WORKORDERID",

"F_PARENTWOBNUM",

"F_TAG",

"F_WORKCLASSID",

"F_WPCLASSID",

Date(Num(F_TIMESTAMP, 10)/86400 + 25569) as F_TIMESTAMP,

//"F_TIMESTAMP",

"F_SEQNUMBER",

"F_USERID",

"F_BOUNDUSERID",

"F_MACHINEID",

"F_EVENTTYPE",

"F_DURATION",

"F_OCCURRENCEID",

"F_TEXT",

"F_WCREVISION",

"F_SERVERSEQNUM",

"F_ORIGINATOR",

"F_WORKFLOWNUMBER",

"F_COMMENT",

"F_RESPONSE",

"F_SUBJECT",

"F_TRACKERSTATUS",

"F_UNIQUEID",

Date(Num(F_STARTTIME, 10)/86400 + 25569) as F_STARTTIME,

Date(Num(F_ENQUEUETIME, 10)/86400 + 25569) as F_ENQUEUETIME,

"F_WFDEADLINE";

SQL SELECT *

FROM "F_SW"."VWLOG15_202 " where F_TIMESTAMP = Trunc(sysdate);

its throw an error

ERROR:

SQL##f - SqlState: 42818, ErrorCode: 4294966895, ErrorMsg: [IBM][CLI Driver][DB2/LINUXX8664] SQL0401N The data types of the operands for the operation "=" are not compatible or comparable. SQLSTATE=42818

SQL SELECT *

FROM "F_SW"."VWLOG15_202 " where F_TIMESTAMP = Trunc(sysdate)

The datatype of time stamp feild is integer in DB2 i tried alot to convert this feild into datetime but feild is not converted yet.

Anybody have idea how i can convert this in query level?

Re: DB2 Conversion

anbu1984 — Fri, 21 Nov 2014 05:32:56 GMT

What are values stored in field F_TIMESTAMP? Is it in format YYYYMMDD?

Re: DB2 Conversion

noman212 — Fri, 21 Nov 2014 05:34:22 GMT

It stored value in DB2 in integer.

F_TIMESTAMP = 1416386424

Re: DB2 Conversion

anbu1984 — Fri, 21 Nov 2014 05:39:05 GMT

Your timestamp is in unix timestamp format.Check this link

Unix timestamp from a string to date

Re: DB2 Conversion

Anonymous — Fri, 21 Nov 2014 05:43:39 GMT

Your error is coming from the SQL being sent to DB2,

SQL SELECT *

FROM "F_SW"."VWLOG15_202 " where F_TIMESTAMP = Trunc(sysdate);

sysdate is an Oracle function you need to use the DB2 equivalant. I am sure you will find it with a quick Google search.

Re: DB2 Conversion

anbu1984 — Fri, 21 Nov 2014 05:49:43 GMT

You can use Current_date in DB2

Re: DB2 Conversion

jonathandienst — Fri, 21 Nov 2014 05:50:39 GMT

Num(F_TIMESTAMP, 10)? I think this should read:

Date(Num(right(F_TIMESTAMP,10))/86400+25569)

HTH

Jonathan

Re: DB2 Conversion

noman212 — Fri, 21 Nov 2014 05:51:19 GMT

I already apply Current Date.But not resolving

Re: DB2 Conversion

anbu1984 — Fri, 21 Nov 2014 05:58:41 GMT

Also you have to change F_TIMESTAMP(integer) to date. Check Jonathan's post below or my previously posted link

Re: DB2 Conversion

noman212 — Fri, 21 Nov 2014 06:11:35 GMT

Not working...

I already did this in load statement but i want to convert Timestamp in query level

Re: DB2 Conversion

anbu1984 — Fri, 21 Nov 2014 06:34:34 GMT

where (date('1970-01-01') + int(F_TIMESTAMP/86400) days) = Trunc(Current_date)

Re: DB2 Conversion

PradeepReddy — Fri, 21 Nov 2014 06:42:36 GMT

try like this...

Select *,

timestamp(date('1970-01-01'), time('00:00:00')) + F_TIMESTAMP AS TIMESTAMP_NEW

From Table1;

Re: DB2 Conversion

jagan — Fri, 21 Nov 2014 06:50:09 GMT

Hi,

Try like this, instead of converting Timenstamp in Qlikview format the date in DB2 itself using Date()

TableName:

SQL SELECT *,

Date(F_STARTTIME) AS F_STARTTIME_FORMATTED,

Date(F_ENQUEUETIME) AS F_ENQUEUETIME_FORMATTED,

Date(F_TIMESTAMP) AS F_TIMESTAMP_FORMATTED

FROM "F_SW"."VWLOG15_202 ";

Hope it helps you.

Regards,

Jagan.

Re: DB2 Conversion

noman212 — Wed, 26 Nov 2014 04:47:31 GMT

Hello All,

I found solution for DB2 Date Conversion Integer to Timestamp.

timestamp('1970-01-01-00.00.00') + (F_TIMESTAMP) seconds as F_TIMESTAMP

thanks for your efforts

Regards