topic Re: Oracle timestamp value is changed at Qlik sense side in QlikView

Oracle timestamp value is changed at Qlik sense side

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

My data source is Oracle and Datatype is Timestamp.

My value at oracle field is: '11-AUG-18 08:38:01.000000000 PM'

When I use your expression the value is changed to: 12-08-2018 06:08:01 AM.

It is noted that I am using the below expression:

Timestamp(UNPACKAGED_DATE_TIME, 'MM/DD/YYYY h:mm:ss.fff TT') as UNPACKAGED_DATE_TIME

and my sql is:

select FILE_ID, UNPACKAGED_DATE_TIME

from edi_e2e_user.abc_e2e_tracking_record where FILE_ID = 'ci1534054081616.19521239@sbycsaxy05_te';

Please advise what should I do now?

Re: Oracle timestamp value is changed at Qlik sense side

arvind1494 — Mon, 13 Aug 2018 08:04:11 GMT

In main script

change SET CollationLocale='en-US'; to

SET CollationLocale='en-IN';

Re: Oracle timestamp value is changed at Qlik sense side

ananyaghosh — Mon, 13 Aug 2018 08:52:52 GMT

Hi,

It is not working for me. Please advise what is causing problem?

Re: Oracle timestamp value is changed at Qlik sense side

arvind1494 — Mon, 13 Aug 2018 09:04:14 GMT

Can you attach your sample qvw

Re: Oracle timestamp value is changed at Qlik sense side

Anil_Babu_Samineni — Mon, 13 Aug 2018 09:37:41 GMT

Does this helps?

=Timestamp(Timestamp#(UNPACKAGED_DATE_TIME,'DD-MMM-YY hh:mm:ss.fffffffff TT'), 'DD-MMM-YY h:mm:ss') & '.' & SubField(SubField(UNPACKAGED_DATE_TIME,' ',-2),'.',-1) & ' ' & SubField(UNPACKAGED_DATE_TIME,' ',-1)

Re: Oracle timestamp value is changed at Qlik sense side

BalaBhaskar_Qlik — Mon, 13 Aug 2018 11:50:09 GMT

May be this:

Date(Floor(TimeStamp#(DateField))) as Date

Re: Oracle timestamp value is changed at Qlik sense side

Vu_Nguyen — Mon, 13 Aug 2018 11:53:59 GMT

Try loading UNPACKAGED_DATE_TIME as text. Which textual value of this field shows up in the raw data?