queries on structured data?

Hi,

I am working for a college. The campus calendar [classes start/end,
vacation start/end, etc] is available to me as an RSS feed. All I
can do is read this data, I can not "query" it. I have a small app
that reads the RSS feed and populates a local table with the RSS info.
This table I can query. The local table looks like:

CREATE TABLE `academic_calendar` (
`id` int(10) unsigned NOT NULL auto_increment,
`date` datetime NOT NULL,
`title` varchar(128) NOT NULL,
`category` varchar(128) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `event` (`date`,`title`,`category`)
)

The records either identify the start/end of a semester or multi-day
break or single day "breaks" or special occasions. My only clues
are in the event titles:

First Day of Class, Fall Semester
Labor Day
Thanksgiving Vacation Begins
Class Resumes
Class Ends for Fall Semester
Final Exams
First Day of Class, Spring Semester
Martin Luther King Day
Spring Vacation Begins
Class Resumes
Class Ends for Spring Semester
... etc ...

I want to do queries on this data that will either be for date=NOW()
or
date=<some future date>. I want to ask questions like:

- is date-X in the Fall 2007 semester?

- what semester is date-X in?

- is date-X a class-day, i.e. is it in a semester but not in a
vacation range or other holiday

- is date-X in a vacation range or is it a holiday

My first attempt to answer "is date-X in the Fall 2007 semester?" was:

SELECT * FROM `academic_calendar`
WHERE `date` BETWEEN(
(SELECT `date` FROM `academic_calendar`
WHERE `title` = 'First Day of Class, Fall Semester'
AND YEAR(`date`) = '2007'),
(SELECT `date` FROM `academic_calendar`
WHERE `title` = 'Class Ends for Fall Semester'
AND YEAR(`date`) = '2007'))

but there is a syntax error. I'm not seeing it unless these
sub-selects are just not allowed here.

Basically, how do I deal with queries on data that is structured
in this way? There are Start/End records and singleton records.
There are Start/End records that "enclose" other records.

I can imagine selecting all future records and then processing them
in my app to find start/end records. Is that the best way to go?
Can DB queries do what I want?

That select above does not like the BETWEEN. I changed it to:

SELECT * FROM `academic_calendar`
WHERE `date` >=
(SELECT `date` FROM `academic_calendar`
WHERE `title` = 'First Day of Class, Fall Semester'
AND YEAR(`date`) = '2007')
AND `date` <=
(SELECT `date` FROM `academic_calendar`
WHERE `title` = 'Class Ends for Fall Semester'
AND YEAR(`date`) = '2007')

and it was fine