Using current system month inside date functions-CodePudding

I am trying to write a query in Noetix that is pulling data from Oracle EBS. The query will have a column that checks to see if the date range of a field value of each record is within the current month then, if so, return another value. For example, the field value might be "23 JUN 2022", and I want to check to see if this date is within the "current" month.

So that I don't have to manually edit the report every time a month turns, I want the function to be 'rolling' where it checks the system time for the current month instead of me hard coding it in. I have the following expression, which works, but is static:

case
when
"TABLE1"."Scheduled_Date" >= TO_DATE('01 Jun 2022','DD Mon YYYY') AND 
"TABLE1"."Scheduled_Date" < TO_DATE('01 Jul 2022','DD Mon YYYY') THEN
"TABLE1"."Selling_Price"
ELSE
TO_NUMBER('0')
END

How do I replace "Jun" and "Jul" in the expression above with a SYSDATE function that returns the current system month (for Jun), and the current system month 1 (for Jul)? I am experienced at MS Access SQL, but Oracle SQL is new to me. I can't figure out the proper syntax.

CodePudding user response：

for the 1st day of the actual month you can use

add_months(last_day(trunc(sysdate)) 1, -1)

for the 1st day of the next month you can use

last_day(trunc(sysdate)) 1

CodePudding user response：

There are various options you might choose; here's one of them (I'm setting date format so that you'd recognize values being returned; you don't have to do that):

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select trunc(sysdate, 'mm') first_of_this_month,
  2         add_months(trunc(sysdate, 'mm'), 1) first_of_next_nonth
  3  from dual;

FIRST_OF_THIS_MONTH FIRST_OF_NEXT_NONTH
------------------- -------------------
01.06.2022 00:00:00 01.07.2022 00:00:00

SQL>

Applied to your code:

case when     "TABLE1"."Scheduled_Date" >=            trunc(sysdate, 'mm')
          AND "TABLE1"."Scheduled_Date"  < add_months(trunc(sysdate, 'mm'), 1) 
          THEN
              "TABLE1"."Selling_Price" 
     ELSE
              TO_NUMBER('0')
END