Unanswered

SQL Connector - Get Rows Filter error

(0) Share

Report

Posted on by Johnnyoc

Hi I'm getting a SQL row of exchange rate date and filtering by certain rates and where the createdOn is today.

I can get it to work aside from the today filter. This is my filter expression:

TransactionCurrencyIdName eq 'US Dollar' or TransactionCurrencyIdName eq 'Australian Dollar' or TransactionCurrencyIdName eq 'Euro' or TransactionCurrencyIdName eq 'South African Rand' and CreatedOn eq @{formatDateTime(utcNow(),'yyyy/MM/ddT00:00:00Z')}

This is the error
An identifier was expected at position 202.
inner exception: An identifier was expected at position 202.

I can only assume tis is related to the data format/now expression can anyone please advise?

The CreatedOn data is in ISO 8601 -

"CreatedOn":"2016-10-25T07:46:03Z"

Categories:

Using flows

I have the same question (0)

All responses (7)

Answers (0)

Sort by

lbendlin 8,655 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Your filter logic is flawed. You need to protect the OR filters from the AND filter.

Is this an on prem SQL or Azure SQL?

Was this reply helpful? Yes No
Johnnyoc 41 on at

Like (0)

Report
Copy link

Link copied!

Ah many thanks. This is the onprem connector.

Was this reply helpful? Yes No
lbendlin 8,655 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
TransactionCurrencyIdName eq 'US Dollar' and CreatedOn eq '@{formatDateTime(utcNow(),'yyyy-MM-dd')}' or TransactionCurrencyIdName eq 'Australian Dollar' and CreatedOn eq '@{formatDateTime(utcNow(),'yyyy-MM-dd')}' or TransactionCurrencyIdName eq 'Euro' and CreatedOn eq '@{formatDateTime(utcNow(),'yyyy-MM-dd')}' or TransactionCurrencyIdName eq 'South African Rand' and CreatedOn eq '@{formatDateTime(utcNow(),'yyyy-MM-dd')}'

Was this reply helpful? Yes No
Johnnyoc 41 on at

Like (0)

Report
Copy link

Link copied!

Hi @lbendlin this doesn't error but returns no values. Which it should. Any other ideas?

Was this reply helpful? Yes No
lbendlin 8,655 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

please provide some sample data that covers all these scenarios.

Was this reply helpful? Yes No
Johnnyoc 41 on at

Like (0)

Report
Copy link

Link copied!

The filters work aside from the dates. The date in the data is
"CreatedOn": "2024-05-09T06:00:08Z"

I've tried filters using both the below that returns nothing using a static date and expression:
CreatedOn eq '2024-05-09T06:00:08Z'

utcnow('yyyy-MM-dd')

Was this reply helpful? Yes No
lbendlin 8,655 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Ha! Solved: SQL GetRows OData Date Filter - Power Platform Community (microsoft.com)

Was this reply helpful? Yes No