Database Reference
In-Depth Information
The meaning of the second constraint is that turn-of-century years are not leap years,
except every fourth century. In SQL, express these conditions as follows:
(YEAR(d) % 4 = 0) AND ((YEAR(d) % 100 <> 0) OR (YEAR(d) % 400 = 0))
Running our date_val table through both the rule-of-thumb leap-year test and the
complete test produces the following results:
mysql> SELECT
-> d,
-> YEAR(d) % 4 = 0
-> AS 'rule-of-thumb test',
-> (YEAR(d) % 4 = 0) AND ((YEAR(d) % 100 <> 0) OR (YEAR(d) % 400 = 0))
-> AS 'complete test'
-> FROM date_val;
+------------+--------------------+---------------+
| d | rule-of-thumb test | complete test |
+------------+--------------------+---------------+
| 1864-02-28 | 1 | 1 |
| 1900-01-15 | 1 | 0 |
| 1999-12-31 | 0 | 0 |
| 2000-06-04 | 1 | 1 |
| 2017-03-16 | 0 | 0 |
+------------+--------------------+---------------+
As you can see, results from the two tests sometimes differ. In particular, the rule-of-
thumb test fails for the year 1900; the complete test result is correct because it accounts
for the turn-of-century constraint.
Because the complete leap-year test must check the century, it requires four-digit year
values. Two-digit years are ambiguous with respect to the century, making it impossible
to assess the turn-of-century constraint.
To make the leap-year test easier to perform in SQL statements, use a stored function
that encapsulates the expression just shown. The routines directory of the recipes
distribution contains a script that creates an is_leap_year() function.
If you work with date values within a program, you can perform leap-year tests with
your API language rather than at the SQL level. Extract the first four digits of the date
string to get the year, then test it. If the language performs automatic string-to-number
conversion of the year value, this is easy. Otherwise, you must explicitly convert the year
value to numeric form before testing it.
Perl, PHP:
$year = substr ( $date , 0 , 4 );
$is_leap = ( $year % 4 == 0 ) && ( $year % 100 != 0 || $year % 400 == 0 );
Search WWH ::




Custom Search