Answered

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

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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?

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

Verified answer

Brian Dang 3,976 on at

Like
a
(3)

Report
Copy link

Link copied!
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
timl 37,287 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous

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
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

The selectedDate gets set :

but the Items table is empty:

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

so I added 4/24 to the variable

Mut still the items array is empty.

How can I get all the records for the most recent date in a table?
timl 37,287 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous

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)
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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.