Help with sorting dates from SQL to Powerapps canvas

[rlavigne99]

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. 

[RandyHayes]

@rlavigne99 

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.

[rlavigne99]

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. 

[RandyHayes]

@rlavigne99 

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"
)

[rlavigne99]

Still no data. 

[RandyHayes]

@rlavigne99 

Errors?

[rlavigne99]

Delegation warning. I think its just not pulling the amount of data that it needs to. 

[RandyHayes]

@rlavigne99 

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?

[rlavigne99]

[RandyHayes]

@rlavigne99 

To validate my statement, here is info from the docs.