DB2 Conversion

[noman212]

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?

[anbu1984]

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

[PradeepReddy]

try like this...

Select *,

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

From Table1;

[jagan]

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.

[noman212 · Accepted solution]

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