cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
wcsmith
Level: Powered On

Oracle Date Filter Error...need to filter column for Prior Year

Hey everyone...really need help on what I'm missing here.  I've added a couple of screenshots to help.  I'm simply trying to filter a date column from a table in an Oracle Data Warehouse to be equal to 1 year ago.  The logic seems correct, but I continue getting the error stating an identifier was expected.  Any thoughts?

 

Flow Error.PNGError2.PNG

12 REPLIES 12
Dual Super User
Dual Super User

Re: Oracle Date Filter Error...need to filter column for Prior Year

Hi @wcsmith 

 

This might be due to date formatting. Can you please check what is the date format on the Oracle DB (LICENSE_EFFECTIVE_DATE column)? The expression you used in flows gives you MM/dd/yyyy. I am guessing this what is causing the error. You can use the formatdatetime()function to appropriately format the date type. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Community Support Team
Community Support Team

Re: Oracle Date Filter Error...need to filter column for Prior Year

Hi @wcsmith ,

 

Please try the following ways:

  1. Make sure the two dates are in the same format, you could use formatDateTime function to format the date returned from the function to the same format with License_effective_date column.
  2. Please surround the function with a single quote. When using function in the Filter query, we are always asked to surround them with a single quote.

 

Hope this could be helpful.

 

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
wcsmith
Level: Powered On

Re: Oracle Date Filter Error...need to filter column for Prior Year

I think I have the formatting the same now....   but I get the following error...

 

We cannot apply operator < to types Text and DateTime.  Below is what I'm getting as a result...

 

 

Capture.PNG

Dual Super User
Dual Super User

Re: Oracle Date Filter Error...need to filter column for Prior Year

@wcsmith 

 

Can you please share a screenshot of the flow? I also wanted to see a sample value from the date column of your database. 

wcsmith
Level: Powered On

Re: Oracle Date Filter Error...need to filter column for Prior Year

Screenshots below...I've tried many different formats and can't seem to get them to work.  I either get the error about missing something in position XX...or I get the error stating it can't do the < operator with DateTime or Text

Flow pic.PNGFLOW...Format date is 'g' I've tried to put ' ' around the function as wellPower BI date.PNGPower BI date resultssql dev date.PNGSQL Developer date results

wcsmith
Level: Powered On

Re: Oracle Date Filter Error...need to filter column for Prior Year

This is what the date looks like in the FLOW if I don't filter anything....

 

"LICENSE_EFFECTIVE_DATE": "2019-09-30T00:00:00Z"
Dual Super User
Dual Super User

Re: Oracle Date Filter Error...need to filter column for Prior Year

@wcsmith 

 

Okay. can you modify your filter statement as LICENSE_EFFECTIVE_DATE ge 'ticks(getPastTime(1,'Year','d'))' ?

 

Edit: you might aso want to have a look at https://docs.microsoft.com/en-us/connectors/oracle/

wcsmith
Level: Powered On

Re: Oracle Date Filter Error...need to filter column for Prior Year


@yashag2255 wrote:

@wcsmith 

 

Okay. can you modify your filter statement as LICENSE_EFFECTIVE_DATE ge 'ticks(getPastTime(1,'Year','d'))' ?

 

Edit: you might aso want to have a look at https://docs.microsoft.com/en-us/connectors/oracle/



That allowed the flow to run, but it did not return any results.  Also...this format yyyy-MM-dd allows the flow to run, but doesn't return any results.  I'm positive results exists, so for some reason it's still not recognizing the date.

Dual Super User
Dual Super User

Re: Oracle Date Filter Error...need to filter column for Prior Year

I see that the date format in the database is  "30-SEP-19" so you can try formatting the date that way. It would be ticks(formatDateTime(getPastTime(1,'Year','d'),'dd-MMM-yy')). Can you check with this expression once?

wcsmith
Level: Powered On

Re: Oracle Date Filter Error...need to filter column for Prior Year

using the 'eq' function...the flow works but I get zero results.  using the 'ge' function..which is what I need...it gives me 

We cannot apply operator < to types Number and DateTime
Dual Super User
Dual Super User

Re: Oracle Date Filter Error...need to filter column for Prior Year

@wcsmith This looks like a limitation in the Oracle DB connection. Have you had a chance to look at this -> 

https://docs.microsoft.com/en-us/connectors/oracle/

 

Hope this Helps!

Seb_Dal
Level: Powered On

Re: Oracle Date Filter Error...need to filter column for Prior Year

I'm not sure it's work for SQL but have you try    endsWith( LICENSE_EFFECTIVE_DATE , '18' )  because i try on an Excel file and it's actually work for your date format but only for 1 year .

endsWith( LICENSE_EFFECTIVE_DATE , '@{formatDateTime( utcNow() ,'yy')}' ) 

 

 

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (7,155)