Database Reference
In-Depth Information
Ruby:
year
=
date
[
0
.
.
3
].
to_i
is_leap
=
(
year
.
modulo
(
4
)
==
0
)
&&
(
year
.
modulo
(
100
)
!=
0
||
year
.
modulo
(
400
)
==
0
)
Python:
year
=
int
(
date
[
0
:
4
])
is_leap
=
(
year
%
4
==
0
)
and
(
year
%
100
!=
0
or
year
%
400
==
0
)
Java:
int
year
=
Integer
.
valueOf
(
date
.
substring
(
0
,
4
)).
intValue
();
boolean
is_leap
=
(
year
%
4
==
0
)
&&
(
year
%
100
!=
0
||
year
%
400
==
0
);
Your API language might provide its own means of determining leap years. For example,
the PHP
date()
function has an
L
option to return whether a date falls in a leap year:
# prevent date () from complaining about not knowing time zone
date_default_timezone_set
(
"UTC"
);
$is_leap
=
date
(
"L"
,
strtotime
(
$date
));
Using leap-year tests for year-length calculations
Years usually have 365 days, but leap years have 366. To determine the length of a year
in which a date falls, use one of the leap-year tests just shown to figure out whether to
add a day. This example uses Perl:
$year
=
substr
(
$date
,
0
,
4
);
$is_leap
=
(
$year
%
4
==
0
)
&&
(
$year
%
100
!=
0
||
$year
%
400
==
0
);
$days_in_year
=
(
$is_leap
?
366
:
365
);
To compute a year's length in SQL, compute the date of the last day of the year and pass
it to
DAYOFYEAR()
:
mysql>
SET @d1 = '2014-04-13', @d2 = '2016-04-13';
mysql>
SELECT
->
DAYOFYEAR(DATE_FORMAT(@d1,'%Y-12-31')) AS 'days in 2014',
->
DAYOFYEAR(DATE_FORMAT(@d2,'%Y-12-31')) AS 'days in 2016';
+--------------+--------------+
| days in 2014 | days in 2016 |
+--------------+--------------+
| 365 | 366 |
+--------------+--------------+
Using leap-year tests for month-length calculations
Recipe 6.14
discusses how to determine the number of days in a month in SQL state‐
ments using the
LAST_DAY()
function. Within an API language, you can write a non
−SQL-based function that, given an ISO-format date argument, returns the number of
days in the month during which the date occurs. This is straightforward except for
