Odata filter query Unrecognized 'Edm.String' literal 'datetime'

39 Views Asked by At

I'm trying to execute a SQL query in Power Automate flow using OData query language. Here's my SQL query:

SELECT 
    column_name_1, 
    column_name_2, 
    column_name_3
FROM 
    table_name
WHERE 
    CAST(Transaction_Date AS DATE) >= DATEADD(day, -7, CAST(GETDATE() AS DATE))
    AND CAST(Amount_in_USD AS FLOAT) >= 10000
    AND TYPE_OF_TRANSACTION = 'PURCHASE'
    AND Transaction_Country = 'US';

Note: The data in the "Transaction_Date" column looks like '2024-03-21'.

Below is how I translated it into OData:

Filter query:

Transaction_Date ge datetime'2024-03-03T00:00:00Z' 
and Amount_in_USD ge 10000 
and TYPE_OF_TRANSACTION eq 'PURCHASE' 
and Transaction_Country eq 'US'

Select query:

column_name_1, column_name_2, column_name_3

However, when I run this, I keep getting an error message:

Unrecognized 'Edm.String' literal 'datetime'2024-03-03T00:00:00Z'' at '31' in 'Transaction_Date ge datetime'2024-03-03T00:00:00Z' and Amount_in_USD ge 10000 and TYPE_OF_TRANSACTION eq 'PURCHASE' and Transaction_Country eq 'US''.

     inner exception: Unrecognized 'Edm.String' literal 'datetime'2024-03-03T00:00:00Z'' at '31' in 'Transaction_Date ge datetime'2024-03-03T00:00:00Z' and Amount_in_USD ge 10000 and TYPE_OF_TRANSACTION eq 'PURCHASE' and Transaction_Country eq 'US''.
clientRequestId: dde40166-d0e8-4e45-b63e-7ee37afe38ba

Can someone help me troubleshoot this issue?

My flow looks like this:

enter image description here

The details of query looks like this: enter image description here

enter image description here

0

There are 0 best solutions below