Teradata Select where date = previous workday/weekday

Question

Using Teradata 15.1x

I'm trying to select data for the previous weekday. I can get the previous day using

cast(cast(cast(a.date_dim_ck as varchar(13)) as date format 'yyyymmdd') as date format 'mm-dd-yyyy') = current_date - 1

but when I try to do a case when to look back 3 days on Monday instead of 1 (to get Friday's data, see below), I get "[3707] Syntax error, expected something like an 'END' keyword between ')' and '='."

where case 
when td_day_of_week(current_date)<>2 
then
cast(cast(cast(a.date_dim_ck as varchar(13)) as date format 'yyyymmdd') as date format 'mm-dd-yyyy') = current_date - 1
else
cast(cast(cast(a.date_dim_ck as varchar(13)) as date format 'yyyymmdd') as date format 'mm-dd-yyyy') = current_date - 3

What am I doing wrong?

Answer 1

You can only use a case statement to determine the value of a field based on another criteria. See syntax below.

where cast(cast(cast(a.date_dim_ck as varchar(13)) as date format 'yyyymmdd') as date format 'mm-dd-yyyy') = case  
    when td_day_of_week(current_date)<>2 
    then
     current_date - 1
    else
     current_date - 3
    end