filter between two dates via a dataset

Ask the community
,
1

Hi team,

I have some code that seems to be working but not fully as expected.

The code works, but doesn’t filter between the dates, it seems to display any item that has a date either with a date greater or less than (so all items .ge) as apposed to between those two search terms

Anyone able to help?

export function searchButton_click(event ) {
filter($w("#courseStartDate").value, $w('#endDate').value);
}


function filter( startDate, endDate) {
let newFilter = wixData.filter();
console.log(newFilter)

if (startDate) newFilter = newFilter.ge('dateOne', startDate).or(newFilter.ge('dateTwo', startDate).or(newFilter.ge('dateThree', startDate))));

if (endDate) newFilter = newFilter.le('dateOne', endDate).or(newFilter.le('dateTwo', endDate).or(newFilter.le('dateThree', endDate))));

$w('#coursesDataset').setFilter(newFilter).then(() => {
let textVariable = $w('#coursesDataset').getTotalCount();
lastFilterSearchBox = searchBox;

})

}

Thank you :slight_smile:

2

Anyone?

3

Hi Stephen,

Date range filters can be pretty tricky, especially one like what you have - taking into consideration three different dates. You have to account for any possible dateTime value for a given date. To do that, the startDate and endDate needs to be modified.

export function filter(startDate,endDate){
   let yearValue = startDate.getFullYear();
   let monthValue = startDate.getMonth();
   let dayValue = startDate.getDate();
   let date1 = new Date(yearValue,monthValue,dayValue,0,0,0);
   yearValue = endDate.getFullYear();
   monthValue = endDate.getMonth();
   dayValue = endDate.getDate();
   let date2=new Date(yearValue,monthValue,dayValue,23,59,59);
   $w("#coursesDataset").setFilter(wixData.filter()
   .between("dateOne",date1,date2)
   .or(
   wixData.filter()
   .between("dateTwo",date1,date2)
   )
   .or(
   wixData.filter()
   .between("dateThree",date1,date2)
   )
   )
   .then(() => {
     console.log("Filtered!");
   });
}
4

@tony-brunsman this works well, thank you. I had been playing with similar code yesterday but not using .between and this made sense once I started to play with and modify for my use.

Thanks again and have a great day!