topic DB2 DateTime conversion in QlikView

DB2 DateTime conversion

arsal_90 — Sun, 20 Sep 2015 20:16:17 GMT

Hi Everyone,

I need help to convert DB2 Date Time field to numeric. Below is the format of data coming:

2015-03-18-15.09.52.249000

2015-03-18-15.09.52.291000

2015-03-18-15.09.52.300000

2015-03-18-15.09.52.314000

2015-03-18-15.09.52.315000

2015-03-18-15.09.52.316000

2015-03-18-15.09.52.321000

2015-03-18-15.09.52.322000

2015-03-18-15.09.52.323000

2015-03-18-15.09.52.325000

2015-03-18-15.09.52.326000

2015-03-18-15.09.52.327000

Now i need it in numeric or else in below format

9/18/2015 6:35:08 PM

Thanks,

Arsal

Re: DB2 DateTime conversion

evan_kurowski — Sun, 20 Sep 2015 20:30:28 GMT

Hello Muhammad,

Really this is mostly impossible, because we lack the appropriate sensor equipment to detect DB2's infrared signature..

but also Timestamp#(TIMES,'YYYY-MM-DD-hh.mm.ss.fff') might pick this up. (*adjust format as necessary)[Times]:

LOAD * INLINE [
TIMES
2015-03-18-15.09.52.249000
2015-03-18-15.09.52.291000
2015-03-18-15.09.52.300000
2015-03-18-15.09.52.314000
2015-03-18-15.09.52.315000
2015-03-18-15.09.52.316000
2015-03-18-15.09.52.321000
2015-03-18-15.09.52.322000
2015-03-18-15.09.52.323000
2015-03-18-15.09.52.325000
2015-03-18-15.09.52.326000
2015-03-18-15.09.52.327000
];

LOAD *, Date(CONVERT,'M/D/YYYY h:mm:ss TT') AS REFORMAT;
LOAD TIMES, Num(Timestamp#(TIMES,'YYYY-MM-DD-hh.mm.ss.fff'),'#####.000000000') AS CONVERT RESIDENT [Times];

Re: DB2 DateTime conversion

satishkurra — Sun, 20 Sep 2015 20:30:48 GMT

Please use the conversation

Date(Date#(ColumnName,'DD/MM/YYYY'))

Try this