Date range filter not working

[agaitan026]

Hi i tried to create a date filter like this

for date column in db im using {{ moment.tz("America/Panama").format() }}

but the filter doesnt works

SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}} WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR idcolaborador = {{ select1.value }}) / filtro por colaborador */

AND ({{ !textInput6.value }}
OR no_factura ILIKE {{ textInput6.value }}) /* filtro por colaborador */

AND ((fecha >= {{ dateRange1.value.start}} and fecha <= {{ dateRange1.value.end}}) OR ({{dateRange1.value.start == ''}} AND {{dateRange1.value.end == ''}})) / DATE FILTER/**

https://www.loom.com/share/75098cd0fdb947c7afb8612483386068 if you see in this video it works when i put like from december 5th to dec 6 and it shows rows with december 5, if i put december 5 to dec 5, doesnt show anything

what im doing wrong?

thank you

[agaitan026]

any tip?

[victoria]

Hey @agaitan026! Hmm, I'm testing a similar query and using the same start and end dates seem to be working for me.

Would you mind sharing a screenshot of the data that your listaRegistrosCombustible query is returning?

[agaitan026]

here is the original query:

and with filter:

and query (without date range)

SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}} WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR idcolaborador = {{ select1.value }}) / filtro por colaborador */

AND ({{ !textInput6.value }}
OR no_factura ILIKE {{ textInput6.value }}) /* filtro por colaborador */

[victoria]

Thank you for sharing that! Would it be alright if I stepped into your app to take a look around? If so, would you mind DM'ing me a link to your app? If not, no worries at all!

[agaitan026]

done query filtered is named: listaRegistrosCombustible_Filtro

[agaitan026]

this is what i got

SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}} as c WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR c.idcolaborador = {{ select1.value }}) / filtro por colaborador */

AND ({{ !textInput6.value }}
OR c.no_factura ILIKE {{ textInput6.value }}) /* filtro por colaborador */

AND ({{ !(dateRange1.endValue&&dateRange1.startValue) }}

OR (c.fecha <= {{moment(dateRange1.endValue).format('YYYY-MM-DD')}}

AND c.fecha >= {{moment(dateRange1.startValue).format('YYYY-MM-DD')}} ))

[agaitan026]

i saw you are using datepicker? i cant see that on retool im using now this:

SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}}

WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR (idcolaborador = {{ select1.value }}) / filtro por colaborador */

AND ({{ !textInput6.value }}
OR no_factura ILIKE {{ textInput6.value }}) /* filtro por colaborador */

AND ({{ !(dateRange1.value.end&&dateRange1.value.start) }}

OR (fecha <= {{moment(dateRange1.value.end).format('YYYY-MM-DD')}}

AND fecha >= {{moment(dateRange1.value.start).format('YYYY-MM-DD')}} )))

[agaitan026]

its working with this

SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}} WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR idcolaborador = {{ select1.value }}) / filtro por colaborador */

AND ({{ !textInput6.value }}
OR no_factura ILIKE {{'%' + textInput6.value + '%'}}) /* filtro por colaborador */

AND ({{ !(dateRange1.value.end&&dateRange1.value.start) }}
OR (fecha <= {{moment(dateRange1.value.end).format('YYYY-MM-DD')}}
AND fecha >= {{moment(dateRange1.value.start).format('YYYY-MM-DD')}} ))

but if i put only Start Date, still shows everything, any chance to modify to accept when user only select Start date?

thank you

[victoria]

Ah, so when the user only selects Start date, you'd like it to use that date for the Start and End, or you'd like to show everything >= Start date?

Of course!

[agaitan026]

both could be possible? i mean if user put start date and not end date, should shows >=, if user put both start and end should show too, this is my current query that works only when i put both start and end

SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}} WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR idcolaborador = {{ select1.value }}) / filtro por colaborador */

AND ({{ !textInput6.value }}
OR no_factura ILIKE {{'%' + textInput6.value + '%'}}) /* filtro por colaborador */

AND ({{ !(dateRange1.value.end&&dateRange1.value.start) }}
OR (fecha <= {{moment(dateRange1.value.end).format('YYYY-MM-DD')}}
AND fecha >= {{moment(dateRange1.value.start).format('YYYY-MM-DD')}} ))

so if i select 2022-12-05 its showing 2022-12-04

[victoria]

How does this look?

I just changed your line 8 to no longer require a start AND end date (line 8 now says if there's no start value, show everything. If there is a start value, we can use the filter!)

[agaitan026]

awesome, works perfect, thank you

[victoria]

Woohoo! So happy to hear that