Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
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;
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...
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
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))
Okay please close this thread.
i replied in other query .
ok..thanks...