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.

How to Get Current Year and Previous Year 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.

Bottom of Form

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. Here’s how:

SELECT EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -12)) AS previous_year

FROM DUAL;

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, 'MM') AS first_day_of_current_month,  -- First day of the current month

    TRUNC(SYSDATE, 'YYYY') AS first_day_of_current_year, -- First day of the current year

    ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) AS first_day_of_next_year, -- First day of next year

    LAST_DAY(SYSDATE) AS last_day_of_current_month, -- Last day of the current month

    LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 11)) AS last_day_of_current_year -- Last day of the current year

FROM

    DUAL;

TRUNC sets the time fields to 00:00:00, effectively finding start and end dates.

Output:

Checking for Leap Years

To test if a year is a leap year in PL/SQL:

WITH current_year AS (
    SELECT EXTRACT(YEAR FROM SYSDATE) AS year
    FROM DUAL
)
SELECT year,
       CASE
           WHEN MOD(year, 400) = 0 THEN 'Leap Year'
           WHEN MOD(year, 100) = 0 THEN 'Not a Leap Year'
           WHEN MOD(year, 4) = 0 THEN 'Leap Year'
           ELSE 'Not a Leap Year'
       END AS if_leap_year
FROM current_year;

This uses the modulo function to determine if 29th February occurs.

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.

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

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

Similar Posts

Leave a Reply

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