Calculating the Number of Days in a Year in Oracle

In Oracle, the number of days in a year can vary depending on whether it's a leap year (366 days) or a non-leap year (365 days). Below are different ways to determine this:

✅ 1️⃣ Basic Calculation for Any Given Year


SELECT TO_DATE('31-DEC-2024', 'DD-MON-YYYY') - TO_DATE('01-JAN-2024', 'DD-MON-YYYY') + 1 AS days_in_year
FROM DUAL;

🔹 This calculates the difference between December 31 and January 1, adding +1 to include both days.

🟢 Output


DAYS_IN_YEAR
------------
366  -- (2024 is a leap year)

✅ 2️⃣ Using `EXTRACT(YEAR FROM SYSDATE)` Dynamically


SELECT TO_DATE('31-DEC-' || EXTRACT(YEAR FROM SYSDATE), 'DD-MON-YYYY') - 
       TO_DATE('01-JAN-' || EXTRACT(YEAR FROM SYSDATE), 'DD-MON-YYYY') + 1 AS days_in_year
FROM DUAL;

🔹 This dynamically calculates the days in the current year.

✅ 3️⃣ Checking If a Year is a Leap Year

You can check whether a specific year has 366 days (leap year) or 365 days (normal year).


SELECT CASE 
           WHEN TO_CHAR(LAST_DAY(TO_DATE('29-FEB-' || EXTRACT(YEAR FROM SYSDATE), 'DD-MON-YYYY')), 'DD') = '29' 
           THEN 366 
           ELSE 365 
       END AS days_in_year
FROM DUAL;

🔹 If Feb 29 exists, it's a leap year (366 days); otherwise, it's 365 days.

🟢 Example Output


DAYS_IN_YEAR
------------
365  -- If it's a normal year
366  -- If it's a leap year

✅ 4️⃣ Using `ADD_MONTHS` Function


SELECT ADD_MONTHS(TO_DATE('01-JAN-2024', 'DD-MON-YYYY'), 12) - TO_DATE('01-JAN-2024', 'DD-MON-YYYY') AS days_in_year
FROM DUAL;

🔹 This adds 12 months to January 1st and subtracts it to get the number of days.

🔹 Summary

Method	Query	Output Example
Basic Static Query	`TO_DATE('31-DEC-YYYY') - TO_DATE('01-JAN-YYYY') + 1`	`365` or `366`
Dynamic Current Year	`EXTRACT(YEAR FROM SYSDATE)`	`365` or `366`
Leap Year Check	`LAST_DAY(TO_DATE('29-FEB-YYYY')) = 29`	`365 / 366`
Using `ADD_MONTHS`	`ADD_MONTHS(TO_DATE('01-JAN-YYYY'), 12) - TO_DATE('01-JAN-YYYY')`	`365 / 366`

Calculating the Number of Days in a Year in Oracle