Converting Date in text format problems

[EpicTriffid]

Hi all,

So I thought I was getting closer to an issue I've been having converting a string to date in order to compare it against today and see if it is in the future.

My date format is dd/MM/yyyy (e.g. 23/11/2023)

When I use the following code in my HTML Table action it works to convert the date, but when i try and use it in the filter query, it gives me the error below:

convertToUtc(concat(split(item()?['FSAGDate'],'/')[2],'-',split(item()?['FSAGDate'],'/')[1],'-',split(item()?['FSAGDate'],'/')[0]), 'Greenwich Standard Time')

"Unable to process template language expressions in action 'Get_items' inputs at line '0' and column '0': 'The template language expression 'convertToUtc(concat(split('FSAGDate','/')[2],'-',split('FSAGDate','/')[1],'-',split('FSAGDate','/')[0]), 'Greenwich Standard Time')' cannot be evaluated because array index '2' is outside bounds (0, 0) of array. "

Any idea why? If I need to covert my date prior to it going in the Get Items Filter Query, I really don't know how to do that...

[ManishSolanki]

Hi @EpicTriffid 

As SharePoint stores the datetime field in ISO format, so we need to query the field with correct format.

Pls use the below filter query:

FSAGDate gt '@{utcNow('yyyy-MM-dd')}'

 Pls copy & past the above query in the flow & retest it.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[EpicTriffid]

Sorry, @ManishSolanki , to clarify, my field is a string showing a date. That's what I'm trying to convert to compare against utcNow()

[ManishSolanki]

Hi @EpicTriffid 

You need add filter array action which perform the filter operation and pass the output body of filter action to the create html table action:

To enter the filter query in "Filter array" action, you need to click on 'Edit in advanced mode' and then enter the below query in the textbox. Using parseDateTime function, we are converting the string to datetime to make it compatible for comparison with utcNow() :

@greater(parseDateTime(concat(split(item()?['FSAGDate'], '/')[2], '-', split(item()?['FSAGDate'], '/')[1], '-', split(item()?['FSAGDate'], '/')[0])), utcNow('yyyy-MM-dd'))

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[EpicTriffid]

Hi @ManishSolanki 

So does that mean I won't be able to use the Filter Query on the Get Items action? If so, no problem, but it keeps giving me a warning if I don't use anything in the Filter Query?

[ManishSolanki]

Hi @EpicTriffid ,

As per my knowledge, you can't use greater than query in "Get items" because of incompatible types (string & datetime) but we can perform 'equals to' operation directly in "Get items" action using the below filter query:

FSAGDate eq '@{utcNow('dd/MM/yyyy')}'

It usually gives warning to use filter query for improving get items performance.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[EpicTriffid]

Thanks @ManishSolanki! That's worked perfectly! A final query:

If I wanted to send the reminder two weeks before the filtered date, how would i do that?

[ManishSolanki · Accepted solution]

Hi @EpicTriffid 

In that case, we will add 14 days to the current date (utcNow) and then equate it with the value in SharePoint string field (FSAGDate) that holds the date. This can be achieved directly using filter query in "Get items":

FSAGDate eq '@{addDays(utcNow(),14,'dd/MM/yyyy')}'

Pls copy & paste the above filter query in the flow.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Ashokkumar450]

@ManishSolanki 

Thank you so much Manish, the solution is really helpful and i am able to extract values from email and create new item in SharePoint list.

Once again thank you so much... 🙂