1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 | CREATE TABLE `seasons` (
`s_id` int(11) unsigned NOT NULL auto_increment,
`s_name` varchar(255) NOT NULL,
`s_date_start` date NOT NULL,
`s_date_end` date NOT NULL,
`s_date_created` datetime NOT NULL,
`s_date_changed` datetime NOT NULL,
`s_created_by` int(11) unsigned NOT NULL,
`s_changed_by` int(11) unsigned NOT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=MyISAM
-- get the number of days per period
set @start = "2007-03-10";
set @end = "2007-09-10";
select *,
TO_DAYS(s_date_end) AS todays_end
,TO_DAYS(s_date_start) AS todays_start
,TO_DAYS(LEAST(s_date_end, @end)) - TO_DAYS(GREATEST(@start, s_date_start)) AS number_of_days_in_season
from seasons
where TO_DAYS(@start) < TO_DAYS(s_date_end)
and TO_DAYS(@end) > TO_DAYS(s_date_start)
|