Answered

SQL query get rows with date

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello, how can i translate this query into filter inside the sql get rows?
I need to run this query every friday and send an email to Manager which employees on Holidays

select author,
CONVERT(date,dateFrom) as dateFrom,
CONVERT(date,dateTo) as dateTo
FROM [dbo].[RequestsList]
where dateFrom >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate()))
AND dateTo < dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

Sort by

v-alzhan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous ，

Does the query are used to get rows from SQL Server table which employees on holidays?

If yes, you could create flow with Get rows action from SQL Server, and you could add a Filter Query action for getting the special rows, then you could create table to the rows and send it to the manager.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Yes, it's exact what i need, but how to translate this filter query into ODATA filter in Get Rows ?

For now i'm doing like this
formatDateTime(utcNow(),'yyyy-MM-dd HH:mm:ss')

But get an error:
')' or ',' expected at position 76 in 'assignedTo eq 'email@emailsite.com'
AND dateFrom ge dateadd(day, 1-datepart(dw, '2020-09-16 10:11:31'), CONVERT(date, '2020-09-16 10:11:31'))
AND dateTo lt dateadd(day, 8-datepart(dw, '2020-09-16 10:11:31'), CONVERT(date, '2020-09-16 10:11:31'))'.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

FInd solution about how to translate my query into ODATA Filter here:

https://powerusers.microsoft.com/t5/Building-Flows/How-do-I-determine-the-Start-and-End-Dates-of-the-current-week/td-p/521321

And now filter looks like this:

Hope it helps to guys, who faced with same problem 😉

Was this reply helpful? Yes No