Filter json sql api using daterange object

Queries and Resources
, , , ,
1

How to filter date column from a json sql api query using date range values? I tried using between in the query but keeps returning empty

2

@Kimhok Welcome to the community!

SELECT * FROM {{formatDataAsArray(yourTable.value)}}
WHERE  ((your_date_column_name >= {{ yourDateRangeField.value.start}} and value_date <= {{ yourDateRangeField.value.end}}) OR ({{yourDateRangeField.value.start == ''}} AND {{yourDateRangeField.value.end == ''}}))
3

I tried this but didn't work since my source is an excel file. Was not able to specify the source. Posting the solution that worked! Thanks!

4

convert(datetime,DateColumn,101) BETWEEN convert(datetime, {{DateTimeRange.value.start}} ,101) AND convert(datetime, {{DateTimeRange.value.end}} ,101)