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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

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