How to get previous year and future year value using EXTRACT and CONNECT BY LEVEL in Oracle Database

SELECT  
   EXTRACT(YEAR FROM SYSDATE) Year 
 , EXTRACT(MONTH FROM SYSDATE) Month 
, EXTRACT(Day FROM SYSDATE) Day 
, EXTRACT(Hour FROM SYSTIMESTAMP ) Hour 
, EXTRACT(MINUTE FROM SYSTIMESTAMP) MINUTE 
, EXTRACT(SECOND FROM SYSTIMESTAMP) SECOND 
, EXTRACT(TIMEZONE_REGION  FROM SYSTIMESTAMP) TIMEZONE_REGION  
, SYSTIMESTAMP  as CurrentTimeStamp
FROM DUAL 


--Logic to get previous 2 years and future 2 years
SELECT EXTRACT(YEAR FROM SYSDATE) + LEVEL AS "YearsList"
 FROM DUAL
 CONNECT BY LEVEL <= 5
 
 
--Logic to get previous 3 days and future 3 days
 SELECT EXTRACT(DAY FROM SYSDATE) -4 LEVEL AS "DayList"
 FROM DUAL
 CONNECT BY LEVEL <= 7

Leave a Reply

Your email address will not be published. Required fields are marked *