month and year string value compared to date in oracle sql

Question

have 2 columns in table

id  CLOSED_DATE
1   11/01/2017
2   12/01/2017
3   12/02/2017
4   01/01/2018
5   02/01/2018

I have a string value of month and year in format "DEC-2017", how do i find which all is in previous months (Id: 1) and in Next months (id:4,5)

i tried like below, but it gave me id 3,4 and 5, because TO_DATE('DEC-2017','MON-yyyy') converted to 01-DEC-2017.

select id from table where TO_DATE('DEC-2017','MON-yyyy') > CLOSED_DATE;

Answer 1

Do what Gordon Said, but Modify the Conditions Slightly

let us create a sample table, smple_t:

    SCOTT@db>CREATE TABLE smple_t
      2      AS
      3          SELECT
      4              ROWNUM id,
      5              ( trunc(SYSDATE) + power(-1,mod(t.rndm,2) ) * t.rndm ) closed_date
      6          FROM
      7              (
      8                  SELECT
      9                      ROWNUM id,
     10                      round(dbms_random.value *-30,0) rndm
     11                  FROM
     12                      dual
     13                  CONNECT BY
     14                      level <= 400
     15              ) t;

Table SMPLE_T created.

Let us check record count:

SCOTT@db>SELECT
  2      COUNT(1) 
  3  FROM
  4      smple_t;
  COUNT(1) 
       400 

Now let us look at the records 1 month preceding DEC-1-2017 to 1 month after (I assume entire month of January).

The "trick" is to make sure if you want all of November that you use the >= operator for the lower boundary and use the < operator for the upper boundary:

SCOTT@db>SELECT
  2      COUNT(1),
  3      add_months(TO_DATE('DEC-2017','MON-YYYY'),-1) all_nov_dates,
  4      add_months(TO_DATE('DEC-2017','MON-YYYY'),2) all_jan_dates
  5  FROM
  6      smple_t
  7  WHERE
  8      1 = 1
  9      AND   closed_date < add_months(TO_DATE('DEC-2017','MON-YYYY'),2)
 10      AND   closed_date >= add_months(TO_DATE('DEC-2017','MON-YYYY'),-1)
 11  GROUP BY
 12      add_months(TO_DATE('DEC-2017','MON-YYYY'),-1),
 13      add_months(TO_DATE('DEC-2017','MON-YYYY'),2);
  COUNT(1) ALL_NOV_DATES             ALL_JAN_DATES             
---------------------------------------------------
       207 01-NOV-2017 12:00:00 AM   01-FEB-2018 12:00:00 AM   

---

Addendum based on update to requirement:

OP indicated that the desired result set is non-December 2017 (or 2016) closed dates.

OP indicated, "It should give me all months id before Dec-2016 excluding Dec-2016.... Nov-16, Oct-16.. and another one result with all future months excluding Dec-2016, i.e., Jan-17,Feb-17"

When we have a disjoint result set needed (in this case with the closed_date), we can use the or operator to to achieve this

It is important to enclose the the conditions around closed_date in parenthesis because AND has operator precedence.

SCOTT@db>with  smple_t as
  2  (        select sysdate - 90 closed_date
  3            from dual
  4           union all
  5          select sysdate - 35 closed_date
  6            from dual
  7           union all
  8          select sysdate + 40 closed_date 
  9          from dual
 10          union all
 11          SELECT SYSDATE - 40 closed_date 
 12          from dual
 13          UNION ALL 
 14          SELECT SYSDATE + 90 closed_date
 15          FROM dual
 16  )
 17  SELECT
 18      closed_date
 19  FROM
 20      smple_t
 21  WHERE
 22      1 = 1
 23      AND   (
 24          closed_date >= add_months(TO_DATE('DEC-2017','MON-YYYY'),1)
 25          OR    closed_date < TO_DATE('DEC-2017','MON-YYYY')
 26      );
CLOSED_DATE               
-----------------------
04-NOV-2017 03:28:17 PM   
14-MAR-2018 03:28:17 PM   
03-MAY-2018 03:28:17 PM   

Answer 2

You want all months except for the one given? Then convert closed_date and compare it with the given string.

select id 
from table 
where to_char(closed_date, 'MON-yyyy', 'nls_date_language=english') <> 'DEC-2017';

Answer 3

Just throw in an add_months():

select id
from table
where add_months(TO_DATE('DEC-2017','MON-yyyy'), 1) >= CLOSED_DATE;