cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

How to filter data less than today's date from oracle db

Hi all

I've field called Expiry date in table, I want to fetch records from table that is below today's date in expiry date field

 

I tried filter query 

C5010E_EXPIRY_DATE gt @{utcNow()} 

 

it's showing operators cannot be used, May I know how to fix this

6 REPLIES 6
Super User III
Super User III

Hello @SumanKoduri

Can you try to remove the filter from the query. And use a filter array action instead




Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




sorry, can you give me an example how to do that

InvalidTemplate. The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@less(item()?['C5010E_EXPIRY_DATE'], utcNow())' failed: 'The template language function 'less' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.
 
 
tried filter array getting this above error
Super User
Super User

Hello @SumanKoduri 

 

In your first try, in the Filter query field, use single quotes around the expression for utcNow() and change your comparison operation from gt to lt 🙂 - the peek code should show this:

"$filter""C5010E_EXPIRY_DATE lt '@{utcNow()}'"
Kind regards, John

it's showing this error

{
  "status"400,
  "message""We cannot apply operator < to types Text and DateTime.\r\n     inner exception: We cannot apply operator < to types Text and DateTime.\r\nclientRequestId: ce2d354c-7698-458f-b165-9fe05a07f7bb",
  "error": {
    "message""We cannot apply operator < to types Text and DateTime.\r\n     inner exception: We cannot apply operator < to types Text and DateTime."
  },
  "source""oracle-wus.azconn-wus-01.p.azurewebsites.net"
}

Hello @SumanKoduri 

 

Ok, so the Oracle connector has a limit on the Filter query 🙂

You can either go with @Jcook proposed solution, it may work.

Or can you investigate if you have an option to create a view in the Oracel database to include only rows that has an expiry date less than current date. I don't know about the Oracle connectors, so you have to investigate if the connector offers to get rows from a view!

Kind regards, John

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (70,867)