Denodo - Unable to case DATE>= addday(cast(now() as date),-365)

Question

I've encountered an issue when trying to obtain the following output: "If x_date >= now-365 then 1 else 0"

My select statement reads:

SELECT
   id,
   x_date,
   CASE x_date
   WHEN x_date >= addday(cast(now() as date),-365) then 1
   else 0
   end as output

I'm receiving an error message that reads: "SQL Error [30100] [HY000]: CASE argument case((xdate,ge,[addday(trunc(cast('date', now(), 'DATE')) '-365')], utc_il8n), 'true', 'false') is not compatible with the rest of the values.

Has anyone else performed a similar operation with dates in a CASE statement? The Addday works fine and returns 2017-01-05.

score 0 · Accepted Answer · answered Jan 05 '18 at 20:54

Issue with the CASE syntax. I think reading this and other sources may have cause the confusion: https://www.techonthenet.com/sql_server/functions/case.php

Should read:

SELECT
   id,
   x_date,
   CASE WHEN x_date >= addday(cast(now() as date),-365) then 1
   else 0
   end as output

Denodo - Unable to case DATE>= addday(cast(now() as date),-365)

1 Answers1