topic Re: Date interpretation in QlikView

Date interpretation

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

Hello,

From my transaction table i am storing data in QVD first.
Now in my QVD i have few date fields and they are in
MM/DD/YYYY hh:mm:ss TT format ,

Now I changed one As_At_Date as (As_At_Date field is in 'DD/MMM/YYYY' format)
DATE(Date#(As_At_Date,'YYYY-MM-DD'),'DD-MMM-YY') AS Date, to get the desired Date field .

after these code , all the date fields are showing me in numeric value such as

Now my problem is if i want to interpret and display dates in 'DD/MM/YYYY' format with
the following code

Time(Frac(Timestamp#( Ac_Start_Time, 'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY') as CreatedDate
or
Timestamp#( Ac_Start_Time, 'MM/DD/YYYY hh:mm:ss TT') as CreatedTimestamp,

I am not getting any value in the list box.

Could you please help me how to display it right in 'DD/MM/YYY' format , because I need to display these field in few places.

Following is my script for your reference:

ExceptionDataFeedRun:
LOAD As_At_Date,
DATE(Date#(As_At_Date,'YYYY-MM-DD'),'DD-MMM-YY') AS Date,
Rec_Source_ID,
Start_Date,
Rec_Start_Time,
Rec_End_Time,
Rec_Status_ID,
Ac_Start_Time,
Time(Frac(Timestamp#( Ac_Start_Time, 'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY') as CreatedDate,
Ac_End_Time,
Ac_Status_ID,
ModifiedDate,
ModifiedUser
FROM
[..\xxx.qvd]
(qvd);

Thank you,

Ashis

Re: Date interpretation

mostwanted123 — Wed, 20 Sep 2017 07:16:31 GMT

Hi Ashis,

For Date try this,

Date(timestamp#(Ac_Start_Time,'M/D/YYYY hh:mm:ss TT'),'DD/MM/YYYY')

Regards,

Pratik

Re: Date interpretation

Anil_Babu_Samineni — Wed, 20 Sep 2017 07:18:44 GMT

What are you getting when you use this simple?

Date(Start_Date) as Start_Date

Re: Date interpretation

ashis — Wed, 20 Sep 2017 07:40:55 GMT

No value I am getting.

Re: Date interpretation

hari8088 — Wed, 20 Sep 2017 07:50:29 GMT

Can you share one or two samples of your date field so that it will be easy to identify.

Re: Date interpretation

ashis — Wed, 20 Sep 2017 07:54:41 GMT

Thank you Anil, it is giving me correct output.

now I can use the desire format with the following syntax

date(Ac_Start_Time,'DD/MM/YYYY hh:mm:ss') as Ac_Start_Time

Than you for your help.

Re: Date interpretation

Anil_Babu_Samineni — Wed, 20 Sep 2017 07:56:27 GMT

Great, You can mark it as correct answer to close this thread

Re: Date interpretation

mostwanted123 — Wed, 20 Sep 2017 07:57:47 GMT

Your Start_Date is in timestamp format.

To get DATE from that field use,

Date(Timestamp(Timestamp#(Start_Date,'M/D/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY') as NewDate

Regards,

Pratik

Re: Date interpretation

ashis — Wed, 20 Sep 2017 08:05:27 GMT

Sure, I will do it , currently it is not showing in my browser.