The Results of Filter Array of Date is Wrong

[dejavuaHK]

Hello everyone, I am updating a power automate flows and want to select a date range (date within next two weeks - from next Monday to next next Friday) for the below trading calendar.  I use the lessorEqual and GreaterorEqual function and I got what I need for the past year data 2022 trading calendar (it has only 2022 dates.).

@And(greaterOrEquals(addDays('12/30/1899', int(item()['Date']), 'MM/dd/yyyy'), outputs('Compose_Day_next_Monday')),lessOrEquals(addDays('12/30/1899', int(item()['Date']), 'MM/dd/yyyy'), outputs('Compose_Day_next_next_Friday')))

As you can see, when I update the data for 2023 and also included a few dates for the 2024, the output is wrong.  The email will always include the dates of 2024，even I just need the dates for two weeks in 2023.  I guess the program only recognizes the date, and ignore the year.  That's why I had this error.  I try to simplify the whole question and change the criteria of filter array by hard coding the "next next Friday" date. However, the error persists.  Please kindly advise if you happen to know what is the reason.  Thanks in advance.

 

[Amit_Sharma]

Hi @dejavuaHK 

Please Use Format date time where you try to compare date.

formatDateTime('01/13/2023','yyyy-MM-dd')

--------------------------------------------------------------------
If I have answered your question, please mark post as Solved.
If you like my response, please give it a Thumbs Up.

[dejavuaHK]

Hi @Amit_Sharma, 

I made the edit but still had the error. I want to get Trading Calendar from 2023-01-02 to 2023-01-13. but the email output is as follows:

[grantjenkins]

Can you look at the output from List rows present in a table and see what the dates look like. Are they all in the format yyyy-MM-dd, or are they just numbers?

Also, are you able to show the full expression you used for addDays in the first Filter array?

[dejavuaHK]

Hi @grantjenkins,

Filter Array full expression: @lessOrEquals(addDays('12/30/1899', int(item()['Date']), 'MM/dd/yyyy'), formatDateTime('01/13/2023', "MM/dd/yyyy")) (as suggested by Amit)

output from List rows present in a table: the date from my Future Time creation is the date format.

[Amit_Sharma]

Hi @dejavuaHK 

Please Select ISO 8601 Format in date time format field of List row present in table Action

After this I think you are good to go.

[grantjenkins]

The date formats need to be yyyy-MM-dd. See changes you should make.

//Change this
@lessOrEquals(addDays('12/30/1899', int(item()['Date']), 'MM/dd/yyyy'), formatDateTime('01/13/2023', "MM/dd/yyyy"))

//To
@lessOrEquals(addDays('12/30/1899', int(item()['Date']), 'yyyy-MM-dd'), formatDateTime('01/13/2023', "yyyy-MM-dd"))

[dejavuaHK]

Hi @grantjenkins , the flow failed to run. I think this is an issue regarding the date filtering. 

Error Details: 

The execution of template action 'Filter_array_from_next_Monday_to_next_next_Friday' failed: The evaluation of 'query' action 'where' expression '@lessOrEquals(addDays('12/30/1899', int(item()['Date']), 'yyyy-MM-dd'), formateDateTime('01/13/2023', 'yyyy-MM-dd'))' failed: 'The template function 'formateDateTime' is not defined or not valid.'.

[grantjenkins]

@dejavuaHK You've spelt formatDateTime as formateDateTime which is causing the error.

formateDateTime('01/13/2023'

[dejavuaHK]

Hi @grantjenkins,

My bad. I think it works now.  But let me check it next week again.  I feel it is still a little bit confused. If the data only contains dates for one year, it works well with 'MM/dd/yyyy'.

But once I update it to 'yyyy-MM-dd', it works well with the data containing multiple years' date.  Thanks anyway.

Happy New year.

[dejavuaHK]

Hi @Amit_Sharma，

I change it from ISO8601 to Serial Number.  And I think you and @grantjenkins  are right. the key issue is the 'yyyy-MM-dd'. 

The format of my excel data file is MM/dd/yyyy, and the starting point of excel beginning date is 12/30/1899. My outputs('Compose_Day_next_next_Friday') and outputs('Compose_Day_next_Monday') are also MM/dd/yyyy. So when I use the function of addDays and formatDatetime, I try to keep it consistent. So going forward, when comparing two dates, I must use 'yyyy-MM-dd' formatting?  Thanks.

Have a happy new year.