Unanswered

Can't filter data in Get Rows using a date column.

(0) Share

Report

Posted on by nhale

I have flow that triggers when a form is submitted. Before saving the form data I need to lookup the employee ID using the Last Name and Date of Birth fields. I can make this work for other columns but not for the Date of Birth. On my SQL server Date of birth is stored as 1972-02-21 00:00:00.000. I can't figure out how to format the date from the form to match correctly.

This query works fine in SQL

Select * From Table
Where FLDLNAME = 'Smith' And FLDBDATE = '1972-2-21'

FLDBDATE in SQL is datetime.

Translated into Flow, even hardcoded will not pull any data when I use the FLDBDATE column. I've tried formatting the date with FormatDateTime, I can see that it formats correctly when it runs but nothing will make it pull the record. Any help greatly appreciated. Thanks.

I have the same question (0)

All responses (8)

Answers (0)

abm abm 32,585 Most Valuable Professional on at

Like (0)

Report

Re: Can't filter data in Get Rows using a date column.

Hi @nhale

Is this OnPrem SQL Server?

Was this reply helpful? Yes No
abm abm 32,585 Most Valuable Professional on at

Like (0)

Report

Re: Can't filter data in Get Rows using a date column.

Hi @nhale

Please have a look at this thread a while ago I help to resolve a similar issue

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

Was this reply helpful? Yes No
nhale 79 on at

Like (0)

Report

Re: Can't filter data in Get Rows using a date column.

Yes. I can get the date from the form formatted to this. It runs without an error. But will not match on the value in SQL server. Thanks

Was this reply helpful? Yes No
abm abm 32,585 Most Valuable Professional on at

Like (0)

Report

Re: Can't filter data in Get Rows using a date column.

Hi @nhale

If you looking to get for a particular date then you may need to implement date less than or greater. The reason for not working is because your date have time as well and you trying to get time matching 00:00:00.000000 so implementing greater and less than dates will get the records you want. Hope it make sense.

Thanks

Was this reply helpful? Yes No
nhale 79 on at

Like (0)

Report

Re: Can't filter data in Get Rows using a date column.

That's staring to make sense but I still need to match on that given day and make sure I don't pull records for a client with the same last name and a birthdate one day away. How would I implement that range? Many thanks for your help.

Was this reply helpful? Yes No
abm abm 32,585 Most Valuable Professional on at

Like (0)

Report

Re: Can't filter data in Get Rows using a date column.

Hi @nhale

Lets split these tasks:

1. Get the filter birthdate

2. Find the filter birth date's yesterday > Here use dd/MM/yyyy formatDateTime and add days minus 1 expressions

3. Find the filter birth date's tomorrow > Here use dd/MM/yyyy formatDateTime and add days plus 1 expressions

4. Get the filter last name

5. Finally using all the above you could construct the Filter using and (AND), le (Less Than), gt (Greater Than) combinations

Was this reply helpful? Yes No
nhale 79 on at

Like (1)

Report

Re: Can't filter data in Get Rows using a date column.

Thanks for your help. This was getting a little crazy just trying to match two dates, should be much easier. I bailed on this approach and created a view in SQL that added a column for the birth date that was date only.

Was this reply helpful? Yes No
abm abm 32,585 Most Valuable Professional on at

Like (0)

Report

Re: Can't filter data in Get Rows using a date column.

Hi @nhale

Thanks for letting me know. If you need any further help let me know.

Was this reply helpful? Yes No