Hi all, very new here. I am trying to load a table via script, and it gives me this error:
ERROR [42846] [Qlik][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42846] ERROR: cannot cast type timestamp with time zone to double precision
This table is a union of 2 tables and one of those has this code in dbt:
WITH marts_finance_event_budgets_date AS (
SELECT
*,
CASE
WHEN "date" ~ '^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$' THEN
TO_DATE("date", 'YYYY-MM-DD')
WHEN "date" ~ '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$' THEN
TO_DATE("date", 'DD/MM/YYYY')
ELSE
NULL
END AS "date_2"
FROM {{ ref('marts_finance_event_budgets') }}
)
SELECT
date_2 AS "date",
_row,
detailed_name::TEXT,
I am suspecting there is something wrong with the date, but I could load it before I think.
Any help, very much appreciated.
Kind regards,
Sergio.