cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rlavigne99
Helper I
Helper I

Help with sorting dates from SQL to Powerapps canvas

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. 

 

rlavigne99_1-1630006153103.png

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. 

rlavigne99_2-1630006357641.png

 

This is the data for context. 

rlavigne99_3-1630006537414.png

 

 

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@rlavigne99 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

20 REPLIES 20
RandyHayes
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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. 

 

rlavigne99_0-1630008139220.png

 

RandyHayes
Super User
Super User

@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"
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Still no data. 

RandyHayes
Super User
Super User

@rlavigne99 

Errors?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

RandyHayes
Super User
Super User

@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?

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

rlavigne99_0-1630009526307.png

rlavigne99_1-1630009565252.png

 

 

RandyHayes
Super User
Super User

@rlavigne99 

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

 

RandyHayes_0-1630009723311.png

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,948)