SQL using dateRange returning erro

💬 Queries and Resources
1

Good morning all,

I am using the following query statement that utilized a dateRange component.

SELECT *
FROM shipping
  LEFT OUTER JOIN pools_built ON shipping.serial_num = pools_built.sequence
WHERE (({{ !dateRange1.value.start }} AND {{ !dateRange1.value.end }}) OR (ship_date BETWEEN {{ dateRange1.value.start }} AND {{ dateRange1.value.end }}))
ORDER BY ship_date, load_num, load_order, dealer

I attempted to run it with nothing having been selected in the dateRange, however, I keep getting the error message ""invalid input syntax for type date: """. Can anyone tell me what is wrong with my SQL statement?

2

Hello @tomm ,

The issue occurs when dateRange1.value.start and dateRange1.value.end are empty, causing SQL errors with NULL or empty comparisons.

  • Added a check for NULL dates: ({{ dateRange1.value.start }} IS NULL AND {{ dateRange1.value.end }} IS NULL).

  • Ensured all records returned if no dates were selected.

  • Retained the BETWEEN condition for valid date filtering.


SELECT *
FROM shipping
  LEFT OUTER JOIN pools_built ON shipping.serial_num = pools_built.sequence
WHERE 
    ({{ dateRange1.value.start }} IS NULL AND {{ dateRange1.value.end }} IS NULL) 
    OR 
    (ship_date BETWEEN {{ dateRange1.value.start }} AND {{ dateRange1.value.end }})
ORDER BY ship_date, load_num, load_order, dealer
1 Like