Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
saivina2920
Creator
Creator

Date Format not coming properly

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...

 

Labels (2)
1 Solution

Accepted Solutions
Chanty4u
MVP
MVP

 

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

View solution in original post

6 Replies
Chanty4u
MVP
MVP

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
Creator
Creator
Author

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
MVP
MVP

 

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
Creator
Creator
Author

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

Date count not coming properly for past 30 days from today
 

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
MVP
MVP

Okay please close this thread. 

i replied in other query .

saivina2920
Creator
Creator
Author

ok..thanks...