cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mh2
Level: Powered On

Select Range between two date

Hi there,

I'm trying to display in a DataTable that takes records from CDS all the rows that have the date field between two dates (chosen by DatePickerStart and DataPickerEnd selection). 

What I can do now is a Filter in which I can select records of one specific date: 

    Filter(checkin; StartsWith(cr5a8_dataoraregistrazione; Text(DatePicker.SelectedDate)));

But how can I extend it in a range of two dates?

Thanks for your help!

8 REPLIES 8
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Select Range between two date

You can use the < and > comparison operators (or <= and >=) to check if a field is between two dates, like in the example below. That will work if the field cr5a8_dataoraregistrazione is of type "Date Only" ("Solo data") or "Date and Time" ("Data e ora") in your CDS entity.

Filter(
    checkin;
    cr5a8_dataoraregistrazione >= DatePicker1.SelectedDate;
    cr5a8_dataoraregistrazione <= DatePicker2.SelectedDate)
MartynLiv
Level: Powered On

Re: Select Range between two date

I tried this and got the following error :

 

The requested operation is invalid. Server Response: We cannot apply operator < to types DateTime and DateTimeZone. Inner exception:  We cannot apply operator < to types DateTime and DateTimeZone. 

Sofie_D
Level 8

Re: Select Range between two date

You should use DateDiff function for comparing dates

More info: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-dateadd-datediff

Filter(
    checkin;
    DateDiff(cr5a8_dataoraregistrazione,DatePicker1.SelectedDate) >= 0 &&
    DateDiff(cr5a8_dataoraregistrazione , DatePicker2.SelectedDate) <= 0)
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Select Range between two date

@MartynLiv which type of data source are you using? Not all connectors support data comparison directly, and depending on which one you have you may need to use a different expression.

MartynLiv
Level: Powered On

Re: Select Range between two date

Hi Carlos,

 

I'm connecting to a SQL table.

 

Regards,

Martyn

MartynLiv
Level: Powered On

Re: Select Range between two date

Thanks Sofie, I'll give it a try.

 

Regards,

Martyn

MartynLiv
Level: Powered On

Re: Select Range between two date

Hi Sofie,

 

That worked. Smiley Very Happy

 

Thankyou very much.

 

Regards,

Martyn

MartynLiv
Level: Powered On

Re: Select Range between two date

Thanks Sofie.

 

That worked Smiley Very Happy

 

Regards,

Martyn