Subtract Dates with Different Formats

[Anonymous]

Hi

I have 2 columns in the Oracle DB having dates with different formats.

First date(Header1) is MM/DD/YYYY

Second Date(Header2) is DD-Mon-YYYY

Ex:

Header 1	Header 2
6/5/2018	20-Jun-18
6/2/2018	1-Jul-18

In Qlikview editor i am loading the dates in the below manner from SQL statement so that the dates appear in the standard format for display purpose and this is coming up fine.

TO_CHAR(TO_DATE(BILL_DATE, 'DD-Mon-YY'),'DD-MON-YYYY') as ACTUAL_BILLING_DATE,

TO_CHAR(ALARM_DATE, 'DD-MON-YYYY') as ALARM_DATE,

Now, in a straight table chart, i want to subtract he dates and get the difference in days only. I tried Date#,Interval and other functions, but nothing seems to be working. Any idea what expression needs to be used?

Thanks

Taher

[vishsaggi]

I would create two more fields with date#() and get int values like

LOAD *,

           Num(Floor(Date#(ACTUAL_BILLING_DATE, 'DD-MON-YYYY))) AS Header1,

           Num(Floor(Date#(ALARM_DATE, 'DD-MON-YYYY))) AS Header2

;

SELECT Field1,

               Field2,

               TO_CHAR(TO_DATE(BILL_DATE, 'DD-Mon-YY'),'DD-MON-YYYY') as ACTUAL_BILLING_DATE,

               TO_CHAR(ALARM_DATE, 'DD-MON-YYYY') as ALARM_DATE

FROM yoursourcetable;

Then just use header2 - header1.

[Anonymous]

Hi,

I tried this. The editor script got reloaded successfully, but the difference didn't work.

It shows a - in the expression.

what i did:

LOAD*,

Num(Floor(Date#(ACTUAL_BILLING_DATE, 'DD-MON-YYYY'))) as Date1,

Num(Floor(Date#(ALARM_MON_YR_DT, 'DD-MON-YYYY'))) as Date2

;

SELECT:

TO_CHAR(TO_DATE(BILL_DATE, 'DD-Mon-YY'),'DD-MON-YYYY') as ACTUAL_BILLING_DATE,

TO_CHAR(ALARM_DATE, 'DD-MON-YYYY') as ALARM_MON_YR_DT,

Reloaded and it was succesfuull.

Then went to Straight table, added the below in calculated dimension:

=Date2-Date1

the column just shows a '-'

[sergio0592]

Hi, in your load script you can try with :

date#(SubField(Header2,'-',1)&'/'&date(date#(SubField(Header2,'-',2),'MMMM'),'MM')&'/'&SubField(Header2,'-',3),'DD/MM/YY')-

date#(SubField(Header1,'/',2)&'/'&SubField(Header1,'/',1)&'/'&right(SubField(Header1,'/',3),2),'DD/MM/YY') as duration

[Anonymous]

HI Jean

I selected the 2 date columns as it is and displayed them in the sheet and this is how they are appearing:

I copied below expression in StraightTable calculated dimension:

=date#(SubField(BILL_DATE,'-',1)&'/'&date(date#(SubField(BILL_DATE,'-',2),'MMMM'),'MM')&'/'&SubField(BILL_DATE,'-',3),'DD/MM/YY')-date#(SubField(ALARM_DATE,'/',2)&'/'&SubField(ALARM_DATE,'/',1)&'/'&right(SubField(ALARM_DATE,'/',3),2),'DD/MM/YY')

But its not working.

[Anonymous]

Hey Jean,

Your solution worked. Thanks a Lot

Taher

[Anonymous]

HI Taher,

Ex:

Header 1	Header 2
6/5/2018	20-Jun-18
6/2/2018	1-Jul-18

First date(Header1) is MM/DD/YYYY

Second Date(Header2) is DD-Mon-YYYY

on top of SQL statement , write  Load * , Alt (Header1, Date(Date#(Header1,'MM/DD/YYYY)) ,Date(Date#(Header1,'DD-MMM-YYYY))  ) AS _Header1,

Alt (Header2, Date(Date#(Header2,'MM/DD/YYYY)) ,Date(Date#(Header2,'DD-MMM-YYYY))  ) AS _Header2;

[vishsaggi]

Can you share a sample where you have this issue. Usually Date#() with Num(Floor()) should help in this scenario, but need some sample so that i can give you and expr that works fine.