Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arsal_90
Creator III
Creator III

DB2 DateTime conversion

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

1 Solution

Accepted Solutions
evan_kurowski
Specialist
Specialist

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

View solution in original post

2 Replies
evan_kurowski
Specialist
Specialist

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
Specialist II
Specialist II

Please use the conversation

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

Try this