General question about filter with dates and formats

(0) Share

Report

Posted on by GeekAlf_Pro

404

Hello,

in aim to limit access to a sql server database, i wanted to filter with dates my datas in a gallery (see here)

i modified my datatype in my database, the dates are now in int and stored like yyyymmdd.

but when i want to filter in my gallery, i have an error message with "value number expected".....

I restarted the gateway without success.

Filter('[dbo].[QSE_Export_Constats]';DateSurvenance >= Value(Text(dp_filtre_items_deb.SelectedDate;"[$-fr-FR]yyyymmdd")) && DateSurvenance <= Value(Text(dp_filtre_items_fin.SelectedDate;"[$-fr-FR]yyyymmdd")))

can you explain me ?

thanks

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!
What about creating a calculated column:

Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column.

See documentation:

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

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.
Verified answer

GeekAlf_Pro 404 on at

Like
a
(0)

Report
Copy link

Link copied!

hi @eka24 ,

i had an error in my view since i modified the datatype.
i corrected it and it works, not fine but it works.
i used a workaround in my formula by *1 the date, that's not perfect.
if i face issues with it, i will try to store a calculated field in my database.

thanks for your time.

have a good day
eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

Since it resolved, mark you response as a solution

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.