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|
Category: Database