TERADATA case when statement in WHERE clause

Question

I need to write a case statement in the WHERE clause, which is - when current_date is 1st of Month then select data BETWEEN 1st day of prev month AND last day prev month ELSE FROM 1st of Curr month till date. I have written this so far but it is not working. '05/01/2017' will be input date.

SELECT *
FROM    MyTable
WHERE calendar_date
    BETWEEN
        CASE WHEN extract (day from CAST( '05/01/2017' AS DATE FORMAT 'MM/DD/YYYY')) =1 --check to see date is 1st of month
        THEN  ADD_MONTHS((CAST( '05/01/2017' AS DATE FORMAT 'MM/DD/YYYY') - EXTRACT(DAY FROM CAST( '05/01/2017' AS DATE FORMAT 'MM/DD/YYYY'))+1), -1) --1st of prev month
                AND ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_date), 0 ) --last day prev month
        ELSE             CAST( '05/01/2017' AS DATE FORMAT 'MM/DD/YYYY') - EXTRACT(DAY FROM CAST( '05/01/2017' AS DATE FORMAT 'MM/DD/YYYY'))+1, 0) --else 1st of Curr mont

        AND CURRENT_DATE
    end
    order by calendar_date

Answer 1

@Dudu, based on your suggestion, I was able to solve the sql. here it is:

SELECT * FROM MYTABLE
                        WHERE CALENDAR_DATE
                            BETWEEN
                                CASE WHEN extract (day from CAST( '05/15/2017' AS DATE FORMAT 'MM/DD/YYYY')) = 1
                                         THEN  (ADD_MONTHS((CAST( '05/15/2017' AS DATE FORMAT 'MM/DD/YYYY') - EXTRACT(DAY FROM CAST( '05/15/2017' AS DATE FORMAT 'MM/DD/YYYY'))+1), -1) )
                                        ELSE          ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 0)
                                END
                            AND 
                                    CASE WHEN extract (day from CAST( '05/15/2017' AS DATE FORMAT 'MM/DD/YYYY')) > 1
                                        THEN     CURRENT_DATE  
                                        ELSE          ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_date), 0 ) 
                                END
                                AND  REPORTNAME_MASTER_ID IN (2565,5216,5364)
                        order by CALENDAR_DATE

Answer 2

select      *

from        mytable

where       calendar_date   between case 
                                        when td_day_of_month (current_date) = 1
                                        then current_date - interval '1' month
                                        else td_month_begin (current_date)
                                    end

                            and     case 
                                        when td_day_of_month (current_date) = 1
                                        then current_date - 1
                                        else current_date 
                                    end


order by    calendar_date