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

1. 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.

2. 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.

3. 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.