topic Date conversion is not working in QlikView

Date conversion is not working

Anonymous — Thu, 04 Feb 2016 07:24:54 GMT

Hi all

i am taking timedate from oracle database

which is in the format as MMM DD YYYY hh:mmTT

for taking count i converted this in qv script like

Date(Floor(Date#(timedate,'MMM DD YYYY hh:mmTT')),'DD/MM/YYYY') as Date

but with this i am getting only few dates

some thing like for 1/15/2016 we have 4 dates in oracle db

but in qlikview it is showing only two

what might be the reason

please help me

regards,

Mahesh

Re: Date conversion is not working

sunny_talwar — Thu, 04 Feb 2016 17:16:53 GMT

Are you seeing null at other places? or the rows are not making into QlikView at all?

Re: Date conversion is not working

swuehl — Thu, 04 Feb 2016 17:22:43 GMT

What's the type in the Oracle DB? If it's DATE, have you just tried

Date(Floor(timedate),'DD/MM/YYYY') as Date

Re: Date conversion is not working

Anonymous — Mon, 08 Feb 2016 04:59:17 GMT

it is showing timedate without any value

Re: Date conversion is not working

Anonymous — Mon, 08 Feb 2016 04:59:55 GMT

the rows are not making into qlikview

Re: Date conversion is not working

sunny_talwar — Mon, 08 Feb 2016 09:07:30 GMT

Can you share your script where it is doing this?

Re: Date conversion is not working

PradeepReddy — Mon, 08 Feb 2016 09:23:57 GMT

try some thing like this...

Date(timestamp#(timedate,'MMM DD YYYY hh:mmTT'),'DD/MM/YYYY') as Date

Re: Date conversion is not working

Mon, 08 Feb 2016 10:12:02 GMT

What does it show, if you just load it without any formatting?

Re: Date conversion is not working

prasadcm — Mon, 08 Feb 2016 12:27:15 GMT

Hi,

I am facing same problem.

I am taking field from oracle database which is in below format

Tue Dec 08 14:02:05 GMT 2015 & datatype is VARCHAR2(30 BYTE).

How can i convert it in DD/MMM/YYYY format?

Re: Date conversion is not working

Michiel_QV_Fan — Mon, 08 Feb 2016 13:34:50 GMT

Maybe not the best solution, more a work around:

date(date#(subfield(oracle_field, chr(32), -1)&subfield(oracle_field, chr(32), 2)&subfield(oracle_field, chr(32), 3), 'YYYYMMMDD'), 'DD/MM/YYYY')

Link this to a master calendar to work with distinct values.

Re: Date conversion is not working

prasadcm — Tue, 09 Feb 2016 05:10:23 GMT

Thanks a lot.

Its working perfectly.

Re: Date conversion is not working

Michiel_QV_Fan — Tue, 09 Feb 2016 08:44:21 GMT

Re: Date conversion is not working

prasadcm — Fri, 12 Feb 2016 10:18:43 GMT

Hi,

I want to include time along with date,

I tried it

date(date#(subfield(RECIEVED_DATE, chr(32), -1)&subfield(RECIEVED_DATE, chr(32), 2)&subfield(RECIEVED_DATE, chr(32), 3)&SubField(RECIEVED_DATE,chr(32),4), 'YYYYMMMDD hh:mm:ss'),

'DD/MM/YYYY hh:mm:ss')

Where am I going wrong?

Re: Date conversion is not working

Michiel_QV_Fan — Mon, 15 Feb 2016 08:52:10 GMT

timesatmp(Timestamp#(...)) instead of date.