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

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);
sqloracledateoracle11g


Solution 1:[1]

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 won't work the way you intended (it is comparing those strings on alphabetical basis), 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.

Solution 2:[2]

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>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Littlefoot

Related Questions