Filter Query - Get Rows from SQL Server

[Anonymous]

Hi everyone, 

I want to Get Rows from SQL Server that have been created precisely three days ago.

The data type of the column in SQL is date (no time). 

I tried the following, and the flow ran successfully, but I don't get any rows?

Date_x0020_Install eq '@{addDays(utcnow('yyyy-MM-dd'),-3)}'

The name of the column in SQL has space. That's why I have added "_x0020_".

Thank you in advance 🙂

[ChristianAbata]

hi @Anonymous with your expression you have this output

2020-09-18T00:00:00.0000000

So I if you need just the date you can try with this expression: 

first(split(addDays(utcnow('yyyy-MM-dd'),-3),'T'))

and your output could be like this:

2020-09-18

[Anonymous]

Hi @ChristianAbata

The flow ran successfully, but again I didn't receive any rows.

My table has data from that date, so that isn't a problem.

[ChristianAbata]

ohh @Anonymous  your date Install is a string o Date format? Please try to get your data without Filter and then try to see what is the format that Power Automate is reading your data could be diferent.

[Anonymous]

Hi @ChristianAbata,

This is the format in SQL

And this is the output in Power Automate:

[ChristianAbata]

thanks @Anonymous  please try with this expression

concat(addDays(utcnow(),-3,'yyyy-MM-dd'),'T:00:00:00Z')

[Anonymous]

Hi @ChristianAbata,

Still no output 😞

Date_x0020_Install eq '@{concat(addDays(utcnow(),-3, 'yyyy-MM-dd' ), 'T:00:00:00Z' )}'

[ChristianAbata]

@Anonymous  please make shure that you have data 19-09-2020 could you do a little search?

[Anonymous]

@ChristianAbata Yes, I have data from that date. 

You had a colon between T and 0 in your expression. I assume that is incorrect?
I removed it, and it still doesn't work. 

[ChristianAbata]

weird @Anonymous  just to test something more, could you leave your SQL silter empty and then after this action can you add a filter array and test with the expression that I gave you?

[Anonymous]

@ChristianAbata Doesn't work either. But I removed the filter and tried to search after the specific date in the output with CTRL + F, and it seems like I don't get all the rows from SQL. 

If I search "Date_x0020_Install", then it can only find 1689 rows, and I have a lot more rows in the table in SQL.

Can it be true that there is a limit?

[ChristianAbata]

ohhh @Anonymous  please try to configure this 

[Anonymous]

@ChristianAbata I get all rows now, but still doesn't work. I tried to hardcode it with the following expression: Date_x0020_Install eq '2020-09-19T00:00:00Z' and that doesn't work either. The filter works with normal text columns but not date 😞

[ChristianAbata]

@Anonymous  please try with the other action

[Anonymous]

Hi @ChristianAbata 

I tried both expressions, and it still doesn't work.

Date_x0020_Install eq '@{concat(addDays(utcnow(),-3,'yyyy-MM-dd'),'T00:00:00Z')}'

Date_x0020_Install eq '@{first(split(addDays(utcnow('yyyy-MM-dd'),-3),'T')))}'

I hope there is a solution to this. 

[ChristianAbata]

@Anonymous  could you share an screanshot?