cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resident Rockstar
Resident Rockstar

MS Flow wont interpret my SQL Get Rows Filter Query

I need to know what is wrong with this!

 

I was able to filter with a text column.  This time one wont work.  I have tried simple text string filters, and they work fine, so it is not really a gateway issue.

 

What I REALLY want to do is a SQL Server - When an item is created and a SQL - hen an item is modified... but MS little secret is that those triggers dont work with on premesis SQL data.  It burns me that they hide this detail.  

 

So, I am trying to create a timer to check dates every 15min, and act accordingly.

 

formatDateTime(adddays(utcnow(),-1),'MM/dd/yyyy h:mm:ss tt')

 

image.png

 

 

image.png

 

 

 

 

12 REPLIES 12
Highlighted
Resident Rockstar
Resident Rockstar

Re: MS Flow wont interpret my SQL Get Rows Filter Query

Seems I had to format the column too...

 

This made it work.

 

'@formatdatetime(LastModified)' ge '@{formatdatetime(adddays(utcnow(),-2))}'

Now, i think i just need to put in the same format on both sides to get it to work.  Its not smart enough to translate internal to the inequality.

Highlighted
Resident Rockstar
Resident Rockstar

Re: MS Flow wont interpret my SQL Get Rows Filter Query

No, it still doenst work.  It doesnt error now, but it doenst result in a correct filter.  Its letting crap through that isnt close.  Still need to know how to write this.

Highlighted
Community Support
Community Support

Re: MS Flow wont interpret my SQL Get Rows Filter Query

Hi @martinav,

 

I want to confirm if the field in the rows is "Modified" or "LastModified",and what is the formate of time in the  "Modified" or "LastModified" filed.

Capture.PNG

Could you please delete the expression in the "Filter Query" field in the "Get rows" action, and run the flow,copy and paste all the output from the "Get rows" action to me?

 

I have made a test on my side on a "get items" action of sharepoint connector,the screenshot of my flow as below:

Capture.PNG

 

The exression as below:

formatDateTime(addDays(utcNow(),-1),'yyyy-MM-ddTHH:mm:ssZ')

The flow would run successfully as below:

Capture.PNG

 

 

 

Please let me know if your problem could be solved or share more details so we would provide a proper workaround for you.

 

Best regards,

Alice

 

 

 

Highlighted
Resident Rockstar
Resident Rockstar

Re: MS Flow wont interpret my SQL Get Rows Filter Query

Here is output as you requested...  I only posted one record.  It just keeps going on...  

 

{"statusCode":200,"headers":{"Pragma":"no-cache","Transfer-Encoding":"chunked","Vary":"Accept-Encoding","x-ms-request-id":"b6d36d14-270e-4140-a56f-10076e346e12","Strict-Transport-Security":"max-age=31536000","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Timing-Allow-Origin":"*","Cache-Control":"no-cache","Date":"Wed, 20 Jun 2018 11:16:51 GMT","X-AspNet-Version":"4.0.30319","X-Powered-By":"ASP.NET","Content-Type":"application/json; charset=utf-8; odata.metadata=minimal","Expires":"-1","Content-Length":"713746"},"body":{"@odata.context":"https://msmanaged-na.azure-apim.net/apim/sql/9154ca0ae295429f90b785ce5600ed26/$metadata#datasets('default')/tables('%5Bdbo%5D.%5Btbl_Drawings%5D')/items","value":[{"@odata.etag":"","ItemInternalId":"aa0a7767-4633-48f8-939f-db2aadbcfd8a","DrawingNumber":"KKM1C","Project":"MW","Component":"GA","Title":"MANWAY ASSEMBLY CS COVER AND NOZZLE WITH CARBON STRAPS","DateDrawn":"2014-10-06T00:00:00Z","idDrawing":"D52351EC-65BB-4674-97D6-7312BC248323","LastModified":"1900-01-01T00:00:00Z","Reviewed":false,"IsActive":false},{"@odata.etag":"","ItemInternalId":"686f4100-5d0e-4946-91fe-57971fe0ec27","DrawingNumber":"KKM1C-BOM","Project":"MW","Component":"BOM","Title":"MANWAY ASSEMBLY, CS COVER AND NOZZLE WITH CARBON STRAPS BOM","DateDrawn":"2014-10-06T00:00:00Z","idDrawing":"1AACF066-3222-4B67-9C17-C3DF1D6E2D22","LastModified":"2015-04-02T00:00:00Z","Reviewed":false,"IsActive":false},{"@odata.etag":"","ItemInternalId":"e0c632ab-d317-4ddb-8d49-a6352944f1e6","DrawingNumber":"204086","Project":"PRV","Component":" FLANGE","Title":"FLANGE ASSEMBLY - RAISED FACE - 3 BOLT","DateDrawn":"2014-10-22T00:00:00Z","idDrawing":"D492D652-8FBD-4671-B101-EB3A8B2FDAA5","LastModified":"2016-02-19T16:54:47.453Z","Reviewed":false,"IsActive":true},{"@odata.etag":"","ItemInternalId":"3611f4eb-c5f0-46d6-be82-

If it is just a matter of copying the date/time format of the code, I have done that too, which did not work.

 

I tried this:

 

'LastModified' eq '@{formatDateTime(adddays(utcNow(),-1), 'yyyy-MM-ddThh:mm:ssZ')}'

It still gives me this:

 

Body
{
  "status"400,
  "message""The DateTimeOffset text '2018-06-19T11:32:09Z'' should be in format 'yyyy-mm-ddThh:mm:ss('.'s+)?(zzzzzz)?' and each field value is within valid range.\r\n     inner exception: The DateTimeOffset text '2018-06-19T11:32:09Z'' should be in format 'yyyy-mm-ddThh:mm:ss('.'s+)?(zzzzzz)?' and each field value is within valid range.\r\n         inner exception: The string '2018-06-19T11:32:09Z'' is not a valid AllXsd value.\r\nclientRequestId: 21919ac3-6131-40b8-a9aa-87b73ae5b010",
  "source""sqlconnectionprovider-westus.tip0-westus.p.azurewebsites.net"
}

 

 

Highlighted
Community Support
Community Support

Re: MS Flow wont interpret my SQL Get Rows Filter Query

Hi @martinav,

 

Please take a try with expression below:

formatDateTime(addDays(utcNow(),-1),'yyyy-MM-ddTHH:mm:ssZ')

 

Please let me know if it works.

 

 

Best regards,

Alice

Highlighted
Resident Rockstar
Resident Rockstar

Re: MS Flow wont interpret my SQL Get Rows Filter Query

Did this exactly as indicated.  Still fails.

 

image.png

Highlighted
Frequent Visitor

Re: MS Flow wont interpret my SQL Get Rows Filter Query

Any update on this thread.. Or how this is solved..

Now facing the same issue... Kindly help

Highlighted
Resident Rockstar
Resident Rockstar

Re: MS Flow wont interpret my SQL Get Rows Filter Query

@vissvess ,

 

To directly answer your question.  No, I have not been successful in doing this with a Get Rows SQL Action.  

 

I have done a Get Items action in Sharepoint that works.  It pulls all of the items that are completed (with the Engineering_Comp field in SP as the trigger).

 

The statement below works fine.  It gets all items that were signed off my engineering in the last hour.  Note, its all done in UTC.

image.png

SP Column settings:

For this case, the Field Engineeirng_Comp in SP is a Date and Time column type, and in Date & time Format, Standard.

 

image.png

 

Note format of date in SP is the same as provided by the statement in Flow.  It is also shown as text.  You also need to be sure you have single quotes (') around any Dynamic Content or Expression objects.  

 

image.png

Highlighted
Frequent Visitor

Re: MS Flow wont interpret my SQL Get Rows Filter Query

This may seem to be fine with Sharepoint.!

My query is regarding the SQL action which throws bad request or format error.

 

Could you please help me with it.!?

Highlighted
Resident Rockstar
Resident Rockstar

Re: MS Flow wont interpret my SQL Get Rows Filter Query

In above:  "To directly answer your question.  No, I have not been successful in doing this with a Get Rows SQL Action.  "

 

I just tried it in the V2 Get Rows action.  If it was going to work in the SQL action, it would use the same syntax as the SP action.  I do not see a way to do this.  Sorry, that is all I have.

Highlighted
Frequent Visitor

Re: MS Flow wont interpret my SQL Get Rows Filter Query

if there is any updates , i get the same problem 

Highlighted
Resident Rockstar
Resident Rockstar

Re: MS Flow wont interpret my SQL Get Rows Filter Query

@oum21 ,

 

I am sorry, no.  I have not found any way to make this work.  I pounded on it so long, I gave up on it.  I was able to create a view that included data from other tables that provided a status indicator.  I now filter by the status indicator, which does not require me to search by latest date, or the use of date functions at all.  I was doing everything the MSFT asked me to do with no success.  

 

Sorry, I was not able to solve this one.

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (12,022)