Forum Discussion

sfshariff's avatar
sfshariff
New Member
9 years ago

Difference between two date columns in Direct Query mode

Hi,

 

I am trying to create a calculated column showing the difference between two date columns (duration, i.e number of days). However, DATEDIFF function is not supported in Direct Query mode. I also tried doing simple subtraction between the dates: datedifference = 1.*([createddate]-[closeddate]) and I get the 'ORA-00904: "c168": invalid identifier. The exception was raised by the IDbCommand interface.' error

 

Is there a workaround to get the difference between two date columns in Direct Query mode?

 

Thanks,

 

Sana

2 Replies

    • Salvador's avatar
      Salvador
      Responsive Resident
      9 years ago

      Hi,

       

      Try creating a column with this (You'll need to replace the column names to the ones you'll use): 

       

      Collections Delay =

          SWITCH (

          TRUE ();

          Invoices[Collection Date] < Invoices[Overdue Date]; DATEDIFF ( Invoices[Collection Date];Invoices[Overdue Date]; DAY )* -1;

          Invoices[Collection Date] > Invoices[Overdue Date]; DATEDIFF ( Invoices[Overdue Date]; Invoices[Collection Date]; DAY );

          0)