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]

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

[noman212]

It stored value in DB2 in integer.

F_TIMESTAMP =  1416386424

[anbu1984]

Your timestamp is in unix timestamp format.Check this link

Unix timestamp from a string to date

[Anonymous]

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.

[anbu1984]

You can use Current_date in DB2

[jonathandienst]

Hi

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

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

HTH

Jonathan

[noman212]

I already apply Current Date.But not resolving

[anbu1984]

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

[noman212]

Not working...

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