cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Kingpin
Kudo Kingpin

selecting Max(date) from sql returns a number instead of a date

Hi,

I have a powerapp backed by a sql on-premise database.  The db has a view  Q_ProductionStatus_VR with a dateTime column called  EndDate 

 

I use the following to get the most recent date:

 

Set(selectedDate,Max('[dbo].[Q_ProductionStatus_VR]',EndDate ))

 

 

The selectedDatevariable created is of type number, I would have expected it to be  a Date;

 

Is this an error? Is there some way to convert the number to a date?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Max() will always return a number even if what you're expecting is a date. 

 

To get it back into a date format, you'll want to use DateTimeValue. BUT DateTimeValue takes a text input 🙂 

 

So you'll do something like this:

Set(selectedDate,DateTimeValue(Text(Max('[dbo].[Q_ProductionStatus_VR]',EndDate ))))

BUT since Max() is an aggregate function, you'll hit delegation issues if you try to run it.

 

So there's an easy way to 'sort' this all out--and it's with the Sort function:

Set(selectedDate,First(Sort('[dbo].[Q_ProductionStatus_VR]',EndDate,Descending)))

This means, "Set the selected date to the record equal to the first record that results if you were to sort the datasource by the EndDate in Descending order."

 

Let me know if this was helpful.

 

Brian

Microsoft Employee
@8bitclassroom

View solution in original post

5 REPLIES 5
Highlighted
Community Champion
Community Champion

Max() will always return a number even if what you're expecting is a date. 

 

To get it back into a date format, you'll want to use DateTimeValue. BUT DateTimeValue takes a text input 🙂 

 

So you'll do something like this:

Set(selectedDate,DateTimeValue(Text(Max('[dbo].[Q_ProductionStatus_VR]',EndDate ))))

BUT since Max() is an aggregate function, you'll hit delegation issues if you try to run it.

 

So there's an easy way to 'sort' this all out--and it's with the Sort function:

Set(selectedDate,First(Sort('[dbo].[Q_ProductionStatus_VR]',EndDate,Descending)))

This means, "Set the selected date to the record equal to the first record that results if you were to sort the datasource by the EndDate in Descending order."

 

Let me know if this was helpful.

 

Brian

Microsoft Employee
@8bitclassroom

View solution in original post

Highlighted

Hi @RussellGove

 

I think @mr-dangs approach of returning the first record, sorted by date is the best approach. According the documentation, I don't think the Max function officially supports date values.

But if you're interested, the numeric value that your formula produces is an expression of the date in 'Unix/POSIX time' format (ie, the number of milliseconds since January 1970). If you were to enter your value into an online converter like this, you would get the actual date .

https://www.timecalculator.net/milliseconds-to-date

Highlighted

Hi,

I set the selectedDate as suggested, and then trried to get all the records for the selecteDate like this:

Capture.PNG 

 

The selectedDate gets set :

Capture.PNG

 

but the Items table is empty:

Capture.PNG

 

I noticed that the selectedDate displayed in powerapps is 4 hours  before the value stored inb the db due to localization:

Capture.PNG

 

so I added 4/24 to the variable 

Capture.PNG

 

Mut still the items array is empty. 

 

How can I get all the records for the most recent date in a table?

Highlighted

Hi @RussellGove

 

Do you need to take into account the time element when returning the latest records? If not, my inclination would be to modify your view so that enddate returns just the date without the time element. It would save you from writing some complex formula and wouldn't need to worry about any of the time localisation conversions that PowerApps carries out.

For example, here's the sort of SQL I would use to return the latest records.

CREATE VIEW Q_ProductionStatus_VR_Latest 
AS
SELECT * FROM Q_ProductionStatus_VR
WHERE 
CAST(EndDate AS Date) = 
(SELECT CAST(MAX(EndDate) AS Date) FROM Q_ProductionStatus_VR)
Highlighted

Yeah, I ended up putting a numeric YYYYMMDD in my views. Now I can select Max on that. Seems like adding the YYYYMMDD  solves a lot of problems when working with dates in powerapps.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (10,500)