Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to detect if a given year is a Leap year in Oracle?
Problem:
You need to determine if any given year is a leap year.
Solution:
There are numerous ways of solving this problem.These include calculations to determine whether February 29 exists in the year, or whether March 1 is the 61st or 62nd day of the year.
Example
SELECT to_number(to_char(sysdate,'YYYY')) Year, CASE WHEN MOD(to_number(to_char(sysdate,'YYYY')),400) = 0 then 'Yes' WHEN MOD(to_number(to_char(sysdate,'YYYY')),100) = 0 then 'No' WHEN MOD(to_number(to_char(sysdate,'YYYY')),4) = 0 then 'Yes' ELSE 'No' END AS "Leap Year?" FROM dual;
Output
2020 Yes
Obviously 2020 is a year to forget and it is a Leap year. Let us try this out on non leap year say for e.g. 2019
Example
SELECT '2019' Year, CASE WHEN MOD(2019,400) = 0 then 'Yes' WHEN MOD(2019,100) = 0 then 'No' WHEN MOD(2019,4) = 0 then 'Yes' ELSE 'No' END AS "Leap Year?" FROM dual;
Output
2019 No
The solution is based on the standard definition of a leap year, derived from the year number. The standard algorithm states that if a year is divisible by four, it is a leap year. However, if the year is also divisible by 100, this overrides the divisible-by-four rule and the year it is not a leap year. Finally, if the year is also divisible by 400, then this overrides all other rules, and the year is a leap year.
The baove sequences of steps are followed in our SQL with a CASE statement.
We use a CASE statement to drive the main body of our leap year detection. We apply the leap year algorithm in reverse within the case statement, to leverage the behavior of the CASE statement. As soon as a matching rule is found, the CASE statement will break. That means we want to evaluate the most important rule first, the one that overrides all others, is the year divisible by 400? Should that be false, we want to evaluate the next most stringent rule–division by 100–and so on.
We perform a modulus division using the MOD function. In the first instance, if the year modulus 400 has no remainder, we know it is exactly divisible by 400, and therefore is a leap year. For this case, we return the string Leap Year, and the CASE statement ends. If our year modulus 400 has a remainder, we know it’s not a leap year, and move on to perform modulus division on the year by 100. If there is no remainder, we know the year is perfectly divisible by 100, and therefore isn’t a leap year. We know this because we wouldn’t have evaluated this part of the CASE expression if the year was also divisible by 400, as the CASE statement would already have terminated.
Similarly, if both modulus divisions have a remainder, we fall through to the third modulus division, which finds the remainder of dividing the year by four. Again, if there’s no remainder, we know the prior two rules have not been matched, and therefore the year is a leap year.
At this point, our CASE statement is exhausted, and we know that the year hasn’t matched any of the explicit rules for finding a leap year.
3K+ Views
To Continue Learning Please Login