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

Comments |0|

Legend *) Required fields are marked
**) You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
Category: Database