Filter gallery, SQL Server, date field. Only show items in between dates

Like (0) Share

Report

Posted on 29 Aug 2017 17:16:58 by Community Power Pla...

I am trying to compare dates to only display results from the selected year.

Here is my setup:

1.Is Label for testing purposes just to see if DateValue gets correct date

Text property of first label:

Text(DateValue("01/01/"&Dropdown1.Selected.Value,"en-US"), DateTimeFormat.LongDate)

Text property of second label:

Text(DateValue("12/31/"&Dropdown1.Selected.Value,"en-US"), DateTimeFormat.LongDate)

2. Its Dropdown1 with Item set to:

[2017,2018,2019,2020]

3. Is gallery with Item set to:

Filter(
    '[dbo].[Holiday]',
    HolidayDate >= DateValue("01/01/"&Dropdown1.Selected.Value,"en-US") &&
    HolidayDate <= DateValue("12/31/"&Dropdown1.Selected.Value,"en-US")
)

I get the error:

An error occurred on the server

How would I solve that?

Here is the Database structure:

CREATE TABLE Holiday (
    HolidayID int IDENTITY(1,1) PRIMARY KEY,
    HolidayDate date NOT NULL,
    HolidayDescription varchar(100) NOT NULL,
    HolidayRealDate date NOT NULL,
    LongWeekendOption bit NOT NULL,
    OptionalHoliday bit NOT NULL
);

Database View:

Categories:

General Questions

I have the same question (0)

All responses (3)

Answers (0)

BitLord69 914 on 30 Aug 2017 at 15:16:30

Like (1)

Report

Re: Filter gallery, SQL Server, date field. Only show items in between dates

Do you have access to the actual database? If so, change the type of the field there, as well as in PA; no need to convert the database in PowerApps itself.

You can do comparisons/filtering. In one app I do the following to get the records from a specific month:

Filter('[dbo].[MaltidsRedovisning]'; Text(_firstDayOfMonth;"[$-sv-SE]yyyy-mm") in Datum) and it works well.

_firstDayOfMonth is a Date in PA that I use to keep track of the current month and Datum is the field name in the DB (of type NCHAR(10)). It ought to be possible to select between two dates too.

If you cannot change the Date format in the DB but want to add the Year field, add it as a calculated field. That way there will be no redundancy, just a slight extra load when SQL Server calculates the field.

I haven't found an answer to the very valid question whether SQL Date works or not and would be very interested in getting an answer too.

Good luck!

Was this reply helpful? Yes No
Community Power Pla... on 30 Aug 2017 at 13:12:43

Like (0)

Report

Re: Filter gallery, SQL Server, date field. Only show items in between dates

Hi @BitLord69

But with your workaround you cannot do date comparisons? You would have to bring the whole database on to the PowerApps, convert them to date format and then do date comparisons. This would work if you have less than 500 records but with bigger databases you would not be able to do any date comparisons.

Is it true that dates form SQL do not work in PowerApps? Is there any workaround where you can use date comparisons using delegation on larger databases?

In my case I want to retrieve only records that fall within the selected year. One workaround I am thinking about is to create a new column called Year which would be char(4) or int and store the Year there, while still storing date separately. This way I would be able to use delegation to browse large databases but this is redundant information. And what if down the line I will need to narrow my search to within few months?

Any insight from Microsoft PA support team?

Was this reply helpful? Yes No
BitLord69 914 on 30 Aug 2017 at 06:45:57

Like (0)

Report

Re: Filter gallery, SQL Server, date field. Only show items in between dates

I never got dates to work with SQL. I don't really have a solution, just a workaround:

Since the apps I'm writing will only be used within Sweden (and I don't need to bother about different date formats), I changed it to strings, NCHAR(10) in the DB, and everything started working just fine.

Good luck!

Was this reply helpful? Yes No