MySQL Cookbook, 2nd Edition

Performing Leap Year Calculations

Problem

You need to perform a date calculation that must account for leap years. For example, the length of a month or a year depends on knowing whether the date falls in a leap year.

Solution

Know how to test whether a year is a leap year, and factor the result into your calculation.

Discussion

Date calculations are complicated by the fact that months don’t all have the same number of days, and an additional headache is that February has an extra day during leap years. This recipe shows how to determine whether any given date falls within a leap year and how to take leap years into account when determining the length of a year or month.

Determining whether a date occurs in a leap year

To determine whether a date d falls within a leap year, obtain the year component using YEAR() and test the result. The common rule-of-thumb test for leap years is “divisible by four,” which you can test using the % modulo operator like this:

YEAR(d) % 4 = 0

However, that test is not technically correct. (For example, the year 1900 is divisible by four, but is not a leap year.) For a year to qualify as a leap year, it must satisfy both of the following constraints:

• The year must be divisible by four.

• The year cannot be divisible by 100, unless it is also divisible by 400.

The meaning of the second constraint is that turn-of-century years are not leap years, except every fourth century. In SQL, you can express these conditions as follows:

(YEAR(d) % 4 = 0) AND ((YEAR(d) % 100 != ...