I'm trying to get it to display but it is fetching me null when the output should be 'YES'

SELECT STRT_DT,END_DT,SYSDATE AS DT,
   CASE
    WHEN  TO_CHAR(STRT_DT,'MON-YYYY') <= TO_CHAR(SYSDATE,'MON-YYYY')
         AND TO_CHAR(END_DT,'MON-YYYY') = TO_CHAR(SYSDATE,'MON-YYYY') THEN 'Yes'
    END AS D
FROM
         (
            SELECT TO_DATE('14-JAN-2022','DD-MON-YYYY') AS STRT_DT
                 , TO_DATE('05-APR-2022','DD-MON-YYYY') AS END_DT
          FROM DUAL);

CodePudding user response：

It is not working because of this line:

TO_CHAR(STRT_DT,'MON-YYYY') <= TO_CHAR(SYSDATE,'MON-YYYY')

you are using 'Comparison Operator' on a string, if it is a date datatype the engine would know what is a 'higher' date between these two but because it is a string this condition will never bee satisfied, simple solution would be to replace it just with:

STRT_DT <= SYSDATE

or if u want to compare only month and year:

TRUNC(STRT_DT,'MM') <= TRUNC(sysdate,'MM')

here we are truncating month so only month and year are relevant for comparison in other words if STRT_DT and SYSDATE are in the same year and month the condition will be satisfied even if the STRT_DT is 'higher' (day from the future of that month) then SYSDATE.

CodePudding user response：

It can't work; you're comparing month names. Switch to a different format model which allows meaningful comparing, e.g. YYYYMM instead of MON-YYYY:

SQL> SELECT STRT_DT,
  2         END_DT,
  3         SYSDATE AS DT,
  4         CASE
  5            WHEN     TO_CHAR (STRT_DT, 'yyyymm') <= TO_CHAR (SYSDATE, 'yyyymm')
  6                 AND TO_CHAR (END_DT, 'yyyymm') = TO_CHAR (SYSDATE, 'yyyymm')
  7            THEN
  8               'Yes'
  9            ELSE
 10               'No'
 11         END AS D
 12    FROM (SELECT TO_DATE ('14-JAN-2022', 'DD-MON-YYYY') AS STRT_DT,
 13                 TO_DATE ('05-APR-2022', 'DD-MON-YYYY') AS END_DT
 14            FROM DUAL);

STRT_DT    END_DT     DT         D
---------- ---------- ---------- ---
14.01.2022 05.04.2022 22.04.2022 Yes

SQL>