Solved: Date Format not coming properly - Qlik Community

saivina2920 · ‎2020-12-10

I am using oracle database for back end.

storing the date value in "DATE" data type in oracle. for example : 28-AUG-20

while bringing the value in qlikview report, it will come as "08/28/2020 04:30:35 AM"

I didn't format anywhere. but, still coming as "08/28/2020 04:30:35 AM"

below is the Qlikview Query for example i have used,

select nvl(EMP_JOIN_DATE,'') AS EMP_JOIN_DATE from table;

I want the format 28/08/2020 during the output in qlikview.

How to format inside the Qlik query from oracle DB...

Chanty4u · ‎2020-12-11

SET TimestampFormat=''MM/DD/YYYY hh:mm:ss TT';

Now you can try this

LOAD ..

date(floor(EMP_JOIN_DATE),'MM/DD/YYYY') as EMP_JOIN_DATE

Chanty4u · ‎2020-12-10

you can try this way use before your sql statement in qlikview

load *,
date(date#(Yourdate,'MM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY') as Newdate;

select nvl(EMP_JOIN_DATE,'') AS EMP_JOIN_DATE from table;

saivina2920 · ‎2020-12-11

i tried below statement. but, coming only "-"(eiphen) symbol only.

Load date(date#(EMP_JOIN_DATE,'MM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY') as EMP_JOIN_DATE;

How...??? pls. give some more clue...

Chanty4u · ‎2020-12-11

SET TimestampFormat=''MM/DD/YYYY hh:mm:ss TT';

Now you can try this

LOAD ..

date(floor(EMP_JOIN_DATE),'MM/DD/YYYY') as EMP_JOIN_DATE

saivina2920 · ‎2020-12-11

Thanks for your reply. it's working. same i have another doubts.

EMP_RELIEVE_DATE
--------------------------
13/07/2020
24/07/2020
07/08/2020

=count(DISTINCT if((Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() and Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() > 20) ),EMP_RELIEVE_DATE))

i am getting count people who are relieving from today and past more than 30 days

I have 3 records in that count. but, I am getting "zero" count

what is the problem in the expressions.

=count(DISTINCT if((Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() and Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() > 30) ),EMP_RELIEVE_DATE))

Chanty4u · ‎2020-12-11

Okay please close this thread.

i replied in other query .

saivina2920 · ‎2020-12-11

ok..thanks...

Date Format not coming properly