Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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