Case Date Filter in Where Clause

I'm trying to return a date range dependent on current_date. The logic is as follows:

If the current month is January then return the beginning of the prior year; otherwise return the beginning of the current year.

The attached code keeps throwing a syntax error. I'm using a NS 64bit ODBC in Alteryx to pull the info.

Code :

and t.TRANDATE between

        --period beg
        (case
            when to_char(current_date, 'MM')='01' then add_months(trunc(current_date, 'YYYY'),-12)
            else trunc(current_date, 'YYYY')
        end)
    and
        --period end
        trunc(current_date,'MM')-1/84600)

Any suggestions on how to clear the syntax error or restructure the statement?

CodePudding user response：

Syntax error? Which syntax error? I don't get any:

SQL> select *
  2  from dual
  3  where sysdate not between
  4    case when to_char(current_date, 'mm') = '01' then add_months(trunc(current_date, 'YYYY'),-12)
  5         else trunc(current_date, 'yyyy')
  6    end
  7    and trunc(current_date, 'mm') - 1/84600;

D
-
X

SQL>

(I used NOT BETWEEN and the DUAL table as I don't have your table(s), just to show that this code works OK).

CodePudding user response：

You can use:

AND t.TRANDATE >= TRUNC(ADD_MONTHS(current_date, -1), 'YYYY')
AND t.TRANDATE <  TRUNC(current_date,'MM')