TO_CHAR function for the timestamp if the month is present in between given months-CodePudding

I have a table TABLE1 with 2 columns (ROLLNO, ADM_DT)

 CREATE TABLE TABLE1(ROLLNO NUMBER(3) NOT NULL, ADM_DT TIMESTAMP(3) NOT NULL);

 INSERT INTO TABLE1 VALUES (100, TO_TIMESTAMP('12-DEC-21 12.00.00.000000000 AM', 'DD-MM-RR HH.MI.SS.FF AM'));
 INSERT INTO TABLE1 VALUES (101, TO_TIMESTAMP('12-AUG-21 12.00.00.000000000 AM', 'DD-MM-RR HH.MI.SS.FF AM'));

  
 ROLLNO      ADM_DT
 ---------------------------
  100     12-DEC-21 12.00.00.000000000 AM
  101     12-AUG-21 12.00.00.000000000 AM

I want to write a select query in such a way that if month present in ADM_DT between Sep and Dec then store year value as (year present in ADM_DT) 1 else store year value as (year present in ADM_DT)

For example, in above table TABLE1:

a) 100 record is having ADM_DT of 12-DEC-2021 (which is between Sep and Dec) so I want to fetch the year as 2022

b) 101 record is having ADM_DT of 12-AUG-2021 (which is between Jan and Aug) so I want to fetch the year as 2021

This is the query I have written at present:

 SELECT ROLLNO, 
        TO_CHAR(ADM_DT,'YYYY') YEAR  
 FROM TABLE1;

This is the expected result:

 ROLLNO   YEAR
 --------------
 100      2022
 101      2021

Looks like I am getting the below result:

 ROLLNO      YEAR
 -----------------
  100        2021
  101        2021

CodePudding user response：

Maybe simply this one:

EXTRACT(YEAR FROM ADD_MONTHS(ADM_DT, 4))

CodePudding user response：

I would use a CASE and modify your query like:

 SELECT ROLLNO 
      , CASE WHEN EXTRACT(MONTH FROM ADM_DT ) >=9 THEN TO_CHAR(ADD_MONTHS(SYSDATE,12),'YYYY')
             ELSE TO_CHAR(ADM_DT,'YYYY')
        END        
   FROM TABLE1;