I have a datatype in SQL "Date" and I am trying to sort by that date -6 months. I cannot seem to figure out what I am doing wrong.
If I comment out the stuff I am trying to add to it. It pulls the data but obviously no sorting on the date I want.
This is the data for context.
The error that I am getting I think is around the data type in SQL vs the data type its trying to compare on in powershell. I am new so I am still learning. Any help would be great. I have been combing the sites trying to find the issue I am causing.
Solved! Go to Solution.
Ah, then you are fine. Your formula should be:
SortByColumns( Filter( InvoiceTracking, InvoiceTracking_Contract_Terms_In_Months > 1 && InvoiceTracking_Contract_End_Date >= DateAdd(Now(), -6, Months) ), "InvoiceTracking_Contract_End_Date" )
But, once you convert to datetimeoffset.
Change the formula to :
SortByColumns( Filter( InvoiceTracking, InvoiceTracking_Contract_Terms_In_Months > 1 && InvoiceTracking_Contract_End_Date >= DateAdd(Now(), -30, Minutes) ), "InvoiceTracking_Contract_End_Date" )
You don't need to convert the types. Also not needed is the Ascending. It's the default.
I hope this is helpful for you.
Thanks for the quick reply. That new code looks good but it doe snot seem to still provide the data. No data was pulled and I get the error below.
Ah yes...it has the time zone info in it.
Try the following instead:
SortByColumns( Filter( InvoiceTracking, InvoiceTracking_Contract_Terms_In_Months > 1 && DateTimeValue(Text(InvoiceTracking_Contract_End_Date)) >= DateAdd(Now(), -30, Minutes) ), "InvoiceTracking_Contract_End_Date" )
Yes, delegation is not an error, just a warning - which is what you had in your posted formula as well.
So, a couple of things here. You can't compare a date to a SQL DateTime datatype. You can if your column is a datetimeoffset datatype.
You originally stated that your column was a Date type. But when I looked at your formula, I saw you were trying to compare to 30 minutes prior to Now()...so I assumed your statement about the Date type to be incorrect as that column would not have time information in it if it was a Date type.
Can you adjust your column to a datetimeoffset type or another compatible type?
To validate my statement, here is info from the docs.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.