Difference between two dates YYMMDD

[Black_Hole]

Hello all,

I would like to know if it's possible to calculate the difference between two dates in a condition IF.

For example: IF (20190911-20180911)> 1 year THEN '90%'.

Please could you tell me if it's possible to do that in a LOAD Statement.

Many thanks in advance for your help.

[Frank_Hartmann]

try like this:

Load *, if(date#(Date1,'YYYYMMDD')-date#(Date2,'YYYYMMDD')>365,'90%','other') as New;
LOAD * INLINE [
    Date1, Date2
    20190911, 20180911
    20190911, 20180411
    20190911, 20180910
];

[Black_Hole]

Hello @Frank_Hartmann ,

Thank you for your quick reply.

Before to try your solution, I have a problem with the format of the date.

Indeed, in my DB, the dates have the format YYMMDD. But, in my excel files, the dates have the format YY/MM/DD.

Is it possible to add this element in your expression below:

if(date#(Date1,'YYYYMMDD')-date#(Date2,'YYYYMMDD')>365,'90%','other') 

In order to standardize the format of the dates and to make properly the difference.

Thank you in advance for your help.

[Frank_Hartmann]

try this:

if(date(date#(Date1,'YYYYMMDD'),'YY/MM/DD')-date(date#(Date2,'YYYYMMDD'),'YY/MM/DD')>365,'90%','other') 

[Frank_Hartmann · Accepted solution]

or maybe this:

Load *, if(date(date#(Date1,'YYYYMMDD'),'YY/MM/DD')-date(date#(Date2,'YYYYMMDD'),'YY/MM/DD')>365,'90%','other') as New,
date(date#(Date1,'YYYYMMDD'),'YY/MM/DD') as Date3,
date(date#(Date2,'YYYYMMDD'),'YY/MM/DD') as Date4;
LOAD * INLINE [
    Date1, Date2
    20190911, 20180911
    20190911, 20180411
    20190911, 20180910
];
DROP Fields Date1,Date2; RENAME Fields Date3 to Date1; RENAME Field Date4 to Date2

[Black_Hole]

Hi @Frank_Hartmann,

I try your last solution and this works perfectly!

Many thanks for your help.