Database Reference
In-Depth Information
February, where the function must return 29 or 28 depending on whether the year is a
leap year. Here's a Ruby version:
def
days_in_month
(
date
)
year
=
date
[
0
.
.
3
].
to_i
month
=
date
[
5
.
.
6
].
to_i
# month, 1-based
days_in_month
=
[
31
,
28
,
31
,
30
,
31
,
30
,
31
,
31
,
30
,
31
,
30
,
31
]
days
=
days_in_month
[
month
-
1
]
is_leap
=
(
year
.
modulo
(
4
)
==
0
)
&&
(
year
.
modulo
(
100
)
!=
0
||
year
.
modulo
(
400
)
==
0
)
# add a day for Feb of leap years
days
+=
1
if
month
==
2
&&
is_leap
return
days
end
See Also
Recipe 12.11
discusses leap-year calculations in the context of date validation.
6.19. Canonizing Not-Quite-ISO Date Strings
Problem
A date is in a format that's close to but not exactly ISO format.
Solution
Canonize the date by passing it to a function that always returns an ISO-format date
result.
Discussion
In
Recipe 6.9
, we ran into the problem that synthesizing dates with
CONCAT()
may pro‐
duce values that are not quite in ISO format. For example, the following statement
produces first-of-month values in which the month part may have only a single digit:
mysql>
SELECT d, CONCAT(YEAR(d),'-',MONTH(d),'-01') FROM date_val;
+------------+------------------------------------+
| d | CONCAT(YEAR(d),'-',MONTH(d),'-01') |
+------------+------------------------------------+
| 1864-02-28 | 1864-2-01 |
| 1900-01-15 | 1900-1-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-6-01 |
| 2017-03-16 | 2017-3-01 |
+------------+------------------------------------+