cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

filter SQL Date: filter on no date / date zero

Not working:

Filter('[dbo].[asiel_verblijf]';vertrek_datum=0)
Filter('[dbo].[asiel_verblijf]';IsEmpty(vertrek_datum))

Filter('[dbo].[asiel_verblijf]';Day(vertrek_datum)=0)

Filter('[dbo].[asiel_verblijf]';IsBlank(vertrek_datum))

Filter('[dbo].[asiel_verblijf]';Text(vertrek_datum)="")

 

If a gallery shows a record/item with a blank vetrek_datum:

If I put a label in a gallery Day(Thisitem.vertrek_datum) it does say "0"'

If I put a label in a gallery Text(Thisitem.vertrek_datum) it does say ""' (nothing)

If I put a label in a gallery IsBlank(Thisitem.vertrek_datum) it does say True

 

What will?

I read messages about date filtering SQL tables and I have to say upfront: changing the SQL database is *not* an option for me.

 

Filtering on vertrek_datum < 01-01-2000 would also work for me but then the server connection throws errors and I read averywhere that sql date comparing with < and > will not work.

 

The only filter that does work is if I filter on a specific date and do

Text(vertrek_datum;"dd-mm-yyyy") = "23-02-2017"

 

vertrek_datum=DateValue("23-02-2017") does not work either

 

Additional info: 

If I do  ClearCollect(collectSQLDates;ShowColumns('[dbo].[asiel_verblijf]';"vertrek_datum")) it appears dates are in format "d-m-yyyy" when inspecting collectSQLDates

Filter('[dbo].[asiel_verblijf]';IsBlank(Text(vertrek_datum;"[$-nl-NL]d-m-yyyy"))) does not work either

 

I I do

Filter('[dbo].[asiel_verblijf]';DateDiff(vertrek_datum;Today())>800)
I get a number of hits where vertek_datum is in the 2017s

If I do

Filter('[dbo].[asiel_verblijf]';DateDiff(vertrek_datum;Today())>1000)

I get no hits, while there are records with vetrek_datum=0

3 REPLIES 3
Highlighted
Resolver II
Resolver II

Re: filter SQL Date: filter on no date / date zero

The problem is too silly to bother with probably.

I worked around it like this

 

First I made a subset

;;UpdateContext({KennelRecords:
LastN(Filter('[dbo].[asiel_verblijf]'
;kennel_asiel_id>=_kennel_asiel_id
&& kennel_asiel_id<= _last_kennel_asiel_id
);500)
})

Since records represent stays in an animal shelter and i want to find animals that did not leave yet (vertrek_datum=0) I may hopefully assume that this filter doesn't exclude animals that have been there extremely long.

Now I have a subset of 500 on which some of the filters that failed earlier do work. That absolutely none worked was because when I was using a filter with warning only the first 500 records were considered and none fell in the right category I guess because of the delegation thingie.

Now I can filter the subset in the items of the gallery like this:

Filter(KennelRecords;Day(vertrek_datum)=0) and I get the results I expected

So if I would add this filter to the previous filter I get a delegation warning and nothing shows up, the animals that have no vertrek_datum (date of leaving) are in the bottom part of the table of course.

mmm I cannot rember so much trouble looking for records with no date from my dbase4,clipper,delphi days; strange idea of progress

Highlighted
Community Support
Community Support

Re: filter SQL Date: filter on no date / date zero

Hi @HansHeintz ,

Is your problem caused mainly by delegation?

When using large data set, you need to use delegable funtions. Otherwise, the app will not perform well.

Using non-delegable functions, the app can only deal with at most 2000 records.

Here's doc about delegation and delegable funtions in SQL Server:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

https://docs.microsoft.com/en-us/connectors/sql/

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Resolver II
Resolver II

Re: filter SQL Date: filter on no date / date zero

Are yout trying to explain to me that it is normal that filtering a table on a date field being empty is a problem?

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,397)