How to Get Current Year and Previous Year in Oracle SQL? – A Comprehensive Guide
Understanding time is critical for effective data analysis in Oracle SQL. Extracting specific timestamps, like the current year or previous one, allows you to spot trends, compare performance, and gain valuable insights from your data.
This comprehensive guide will explore multiple methods for retrieving the current and prior years in Oracle SQL.
Retrieving the Current Year with SYSDATE
The easiest way to get the current year is by using Oracle’s built-in SYSDATE function. SYSDATE returns the current timestamp – think of it as a digital clock tracking date and time.
SELECT EXTRACT(YEAR FROM SYSDATE) AS Current_Year FROM DUAL;
The EXTRACT function retrieves the year portion from SYSDATE. This integer value representing the current year is returned for usage in queries.
Getting Previous Year with Date Arithmetic
Date arithmetic allows easily calculating prior years from the current timestamp. Subtracting 1 year using the ADD_MONTHS function achieves this:
SELECT EXTRACT(YEAR FROM SYSDATE – INTERVAL ‘1’ YEAR) AS Previous_Year FROM DUAL;
By subtracting 12 months and extracting the year component, last year’s value is retrieved.
Filtering by Year with WHERE Clause
Combine SYSDATE and date math within WHERE clauses to filter data by year values:
SELECT *
FROM Orders
WHERE
EXTRACT(YEAR FROM Order_Date) = EXTRACT(YEAR FROM SYSDATE) OR
EXTRACT(YEAR FROM Order_Date) = EXTRACT(YEAR FROM SYSDATE – INTERVAL ‘1’ YEAR);
This returns all orders placed either this year or last year. The OR condition checks both current and previous year values returned from date manipulation.
Targeting Beginning and End of Years
The TRUNC function extracts start and end timestamps for current and prior year ranges:
SELECT
TRUNC(SYSDATE, ‘YEAR’) AS Current_Year_Start,
TRUNC(SYSDATE + INTERVAL ‘1’ YEAR, ‘YEAR’) AS Current_Year_End,
TRUNC(SYSDATE – INTERVAL ‘1’ YEAR, ‘YEAR’) AS Previous_Year_Start,
TRUNC(SYSDATE, ‘YEAR’) – INTERVAL ‘1’ DAY AS Previous_Year_End
FROM DUAL;
TRUNC sets the time fields to 00:00:00, effectively finding start and end dates.
Zeroing in on Specific Months
Combine TRUNC and ADD_MONTHS to target particular months within year ranges:
SELECT *
FROM Sales
WHERE Sale_Date BETWEEN
TRUNC(SYSDATE, ‘YEAR’) + INTERVAL ‘2’ MONTH AND
TRUNC(SYSDATE – INTERVAL ‘1’ YEAR, ‘YEAR’) + INTERVAL ’11’ MONTH;
This retrieves sales from March this year to November last year. The BETWEEN clause defines the date range while ADD_MONTHS pinpoints the months.
Building Dynamic Date Ranges
For flexible year boundaries based on external input, utilize parameterized queries:
DECLARE
v_current_year NUMBER := EXTRACT(YEAR FROM SYSDATE);
BEGIN
SELECT *
FROM Customers
WHERE Registration_Date BETWEEN
TO_DATE(’01-JAN-‘ || v_current_year, ‘DD-MON-YYYY’) AND
TO_DATE(’31-DEC-‘ || v_current_year, ‘DD-MON-YYYY’);
END;
Here v_current_year is initialized to the current year value. The TO_DATE function then converts the concatenated strings into valid dates for the year range.
Using the ADD_YEARS Function
The ADD_YEARS function directly adds or subtracts years from a date:
SELECT ADD_YEARS(SYSDATE, -1) AS Previous_Year FROM DUAL;
By passing -1 as the second argument, it returns a DATE value of 1 year before SYSDATE. This provides another option beyond INTERVAL.
Leveraging Object Types
Object types also enable retrieving current and prior years:
CREATE TYPE Year_Range AS OBJECT (
Current_Start DATE,
Current_End DATE,
Previous_Start DATE,
Previous_End DATE
);
SELECT Year_Range(
TRUNC(SYSDATE, ‘YEAR’),
TRUNC(ADD_MONTHS(SYSDATE, 12), ‘YEAR’),
TRUNC(ADD_MONTHS(SYSDATE, -12), ‘YEAR’),
TRUNC(SYSDATE, ‘YEAR’)
) AS Current_Previous_Years FROM DUAL;
The Year_Range object encapsulates the date ranges. Its constructor method initializes the boundaries.
Checking for Leap Years
To test if a year is a leap year in PL/SQL:
DECLARE
v_year NUMBER := EXTRACT(YEAR FROM SYSDATE);
BEGIN
IF (MOD(v_year, 4) = 0 AND MOD(v_year, 100) != 0) OR MOD(v_year, 400) = 0 THEN
DBMS_OUTPUT.PUT_LINE(v_year || ‘ is a leap year’);
ELSE
DBMS_OUTPUT.PUT_LINE(v_year || ‘ is NOT a leap year’);
END IF;
END;
This uses the modulo function to determine if 29th February occurs.
Here, an employee’s number of years with the company is calculated by finding the difference between the current year and the hire year.
FAQs – Frequently Asked Questions and Answers
- How to handle dates with fractional seconds?
Answer: Oracle’s SYSDATE function captures the timestamp with fractional seconds. When comparing dates to the current year or previous year, consider rounding or truncating the date to avoid missing data due to milliseconds.
- How to compare data across fiscal years instead of calendar years?
Answer: You can define your fiscal year start and end dates using specific months (e.g., April to March) and filter data based on these custom date ranges within your queries.
- Is there a way to find the difference between two timestamps in a readable format?
Answer: Utilize functions like EXTRACT and calculations to determine the difference in years, months, days, hours, minutes, and even seconds between two timestamps. You can then format the output for easy interpretation, like “3 years, 2 months, and 14 days.
To Conclude
This article covers the key methods for retrieving current and prior year values in Oracle SQL from functions like SYSDATE to dynamic parameterized date ranges. With a robust temporal toolkit, you can now analyze data across custom timeframes.
