Filtering Date columns in SQL

(0) Share

Report

Posted on by Nicci

194

Hi All, this is driving me crazy and I'm hoping someone out there can help!

I have a SQL on prem database with a column called 'ScannedDate' which is just a date column (not datetime). In my flow I need to select all rows with a date that is passed through Powerapp. I have the format of the date passing through as yyyy-mm-dd.

I've tried everything for the filter query field in my get rows step, I even tried hardcoding it to say:

ScannedDate eq '2024-01-30'

and absolutely nothing! If i do a query in SQL directly with that format it works fine.

Please help!!!

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

wskinnermctc 6,519 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
If you look at the SQL Connector Known Issues and Limitations it shows that datetime is not a query option.

The following data types can't be used as query option predicates:

date

datetime

datetime2

smalldatetime

If you look at PowerApps Delegable Functions to SQL there is a way to Filter.

Direct date filters don't work for SQL Server. However, you can create a calculated column that will work.

For example, you can create ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])), and then filter on the calculated number column.

I don't know how to do that, but it is a method you could explore as an option.

An alternative is to Get Rows from the SQL table and then add a Filter Array action after where you can filter by date. However, this requires the Get Rows to get all of the table rows (or Get Rows filtered by some other data) then the Filter Array action is applied using date.

Was this reply helpful? Yes No
Nicci 194 on at

Like
a
(0)

Report
Copy link

Link copied!
I can do the filter array if it handles dates second as I'm filtering on 2 things in my query. However when I added the filter array its still not finding a match. The output of the initial get rows shows this:

"ScannedDate": "2024-01-30T00:00:00Z"
The date I'm passing from power apps looks like this "2024-01-30". Is the no match happening now because of the format its returning? How do I format it properly for it to get a match?

Was this reply helpful? Yes No
Nicci 194 on at

Like
a
(1)

Report
Copy link

Link copied!

Got it to work! I had to format both the date in the column and the date being passed through so they are the same format and now it works 🙂 thanks!

Was this reply helpful? Yes No