DB2 DateTime conversion

[arsal_90]

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

[evan_kurowski · Accepted solution]

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];

[satishkurra]

Please use the conversation

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

Try this