Determining The Leap Year In Oracle

Finding the Leap Year in Oracle or how to find leap year in oracle SQL

open sql*plus login user name with your respected password after that .

find the sysdate from Dual. It gives the respected First day of the Month. As Shown below when you use Number Function that is Trunc. If you use the To_Char function then it gives the same day of the month when you executed the query.


SQL> SELECT TRUNC(SYSDATE) FROM DUAL;

TRUNC(SY                                                                                            
--------                                                                                            
01-02-17                                                                                            

Get First month of the Year
after finding the sysdate then give the Year Format like 'yyyy' to get first month of the year. But use the Number Function that is TRUNC on date format.

SQL> SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL;

TRUNC(SY                                                                                            
--------                                                                                            
01-01-17    
                                                                                    
Add Months to the above Query to get February                                                                            
In this step we are adding the months to the first month of the year then we get the second month of the year . to get second month then use Date Function that is "ADD_MONTHS".

SQL> SELECT ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),1) FROM DUAL;

ADD_MONT                                                                                            
--------                                                                                            
01-02-17                                                                                          

Find the last day of Month in February
after finding the second month that is February then find the last day of the month and use the "Date Function" that is "LAST_DAY".

SQL> SELECT LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),1)) FROM DUAL;

LAST_DAY                                                                                            
--------                                                                                            
28-02-17                                                                                          

Extract the Day of the Month
After get the last day of the month then we get that date. After finding last day of the month of February then Extract the Day of that particular day.

SQL> SELECT EXTRACT(DAY FROM LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),1))) FROM DUAL;

EXTRACT(DAYFROMLAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),1)))                                       
-------------------------------------------------------------                                       
                                                           28                                       

Compare with Date by using DECODE
Finally we got the last day now compare with 29 th day of February. If the Date is 29 then it is a Leap Year or it is a Non-Leap Year. We have one option to compare it that is DECODE.

SQL> SELECT DECODE(EXTRACT(DAY FROM LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),1))),29,'THIS IS LEAP YEAR ','THIS IS NOT LEAP YEAR') FROM DUAL;

DECODE(EXTRACT(DAYFRO                                                                               
---------------------                                                                               
THIS IS NOT LEAP YEAR                                                                               






Top Searching Topics
Types of indexes in oracle with example