Converting Date in text format problems

(1) Share

Report

Posted on by EpicTriffid

364

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...

All responses (8)

Answers (1)

Ashokkumar450 9 on at

Like (0)

Report

Re: Converting Date in text format problems

@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... 🙂
Verified answer

ManishSolanki 15,085 Super User 2025 Season 1 on at

Like (0)

Report
Re: Converting Date in text format problems

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
EpicTriffid 364 on at

Like (0)

Report

Re: Converting Date in text format problems

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 15,085 Super User 2025 Season 1 on at

Like (0)

Report
Re: Converting Date in text format problems

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 364 on at

Like (0)

Report

Re: Converting Date in text format problems

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 15,085 Super User 2025 Season 1 on at

Like (0)

Report
Re: Converting Date in text format problems

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 364 on at

Like (0)

Report

Re: Converting Date in text format problems

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 15,085 Super User 2025 Season 1 on at

Like (0)

Report
Re: Converting Date in text format problems

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