"cannot cast type boolean to date" sql error

Ah, that makes sense. Some of these solutions seem so easy that I want to kick myself for missing them.

If I may, I have one other question. I have been working with the following SQL statement and for some reason I keep getting a "cannot cast type boolean to date" error.

SELECT *
FROM shipping
WHERE CASE
	WHEN {{ time.value }} = 'day' 
	THEN ({{ !date79.value }} OR ship_date = {{ date79.value }})
	WHEN {{ time.value }} = 'week'
	THEN ship_date BETWEEN {{ moment(new Date()).startOf('day') }} AND {{ moment(new Date()).add(7, 'days').endOf('day') }}
	WHEN {{ time.value }} = 'month'
	THEN EXTRACT(MONTH FROM shipped::date) = {{ select62.value }}
	END;

Any idea what I am doing wrong?

Hey Tomm,

So SQL is not my strenght, but I think the error may be ocurring because of this condition:

({{ !date79.value }} OR ship_date = {{ date79.value }})

From a quick ChatGPT prompt, I got the following:


The expression {{ !date79.value }} evaluates to a boolean (true/false), and SQL cannot compare or mix booleans with dates in a valid expression. Specifically:

  1. {{ !date79.value }} negates the date79.value value (likely a boolean), which is not directly compatible with the rest of the SQL logic.
  2. SQL expects all expressions in the THEN clause to produce valid, comparable conditions, not mixed data types.

How to Fix

You need to refactor the condition to properly handle whether date79.value is empty or has a valid value. Assuming date79.value is a date or null:

SELECT *
FROM shipping
WHERE CASE
    WHEN {{ time.value }} = 'day' THEN 
        ({{ date79.value ? `ship_date = '${date79.value}'` : 'TRUE' }})
    WHEN {{ time.value }} = 'week' THEN 
        ship_date BETWEEN {{ moment(new Date()).startOf('day') }} AND {{ moment(new Date()).add(7, 'days').endOf('day') }}
    WHEN {{ time.value }} = 'month' THEN 
        EXTRACT(MONTH FROM ship_date::date) = {{ select62.value }}
END;

Explanation

  1. {{ date79.value ? \ship_date = '${date79.value}' : 'TRUE' }}:
  • If date79.value is truthy (has a value), it generates ship_date = 'date79.value'.
  • If date79.value is falsy (e.g., null or empty), it generates TRUE, which ensures the condition does not filter any rows.
  1. Ensure ship_date is properly cast and compatible with date79.value if needed, depending on your database.

Hope this helps!

1 Like

I have been trying to set up my table to use the built in add row function and also make the cells editable. So I have a query set up in the table Add-Ons, under Save Action as in the screenshot below.

Screenshot 2025-02-06 090642

I have added the Add Row button to the table toolbar.

Screenshot 2025-02-06 090813

Under Save Actions, I have the query for editing cells set up like this:

Screenshot 2025-02-06 091002

And I have the query for adding a row set up like this:

Screenshot 2025-02-06 091116

When I tested the Add Row function, absolutely nothing happened. No record got inserted. The little blue triangles in the corners of the cell did not even disappear. I tested editing cells and that did not work either.

Can anyone tell me what I am doing wrong?

Hey tomm,

Following up here. Is this still something you're working on? Can you please send screenshots of query2 and query9?