Oracle SQL – Define the year element of a date dependent on the current month

I am trying to create a view in SQL Developer based on this statement:

SELECT * FROM ORDERS WHERE START_DATE > '01-JUL-2020'

The year element of the date needs to set to the year of the current date if the current month is between July and December otherwise it needs to be the previous year.

The statement below returns the required year but I don’t know how to incorporate it (or a better alternative) into the statement above:

select
    case
      when month(sysdate) > 6 then 
        year(sysdate)
      else
        year(sysdate)-1
    end year
  from dual

Thanks

Answer

Oracle doesn’t have a built-in month function so I’m assuming that is a user-defined function that you’ve created. Assuming that’s the case, it sounds like you want

where start_date > (case when month(sysdate) > 6
                         then trunc(sysdate,'yyyy') + interval '6' month
                         else trunc(sysdate,'yyyy') - interval '6' month
                      end)