DB2 Time Conversion

[noman212]

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.

[MK_QSL]

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:

[ashfaq_haseeb]

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,

or

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

[its_anandrjs]

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,

Or

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

NewCHECKEDINDATETime,

Regards

Anand

[#5]

how are you connecting to FileNet database