In SQL how to determine whether a date falls within boundaries

[Phil Davis]

Hi I'm struggling to find an elegant way of determining whether the date from a record falls within a number of date boundaries ranges.

For example in a separate (date boundaries) table I code the "Winter Season" running from 1/11 to 28/2 and the "Summer Season" running from 1/4 to 31/7. I have day1, day2... and month1, month2... fields for each season

I'm try to build something like Select * from tab1 where date between 1/11/year(date) and 28/2/year(date) except I get the day and month numbers from the date boundaries table using a sub select.

Using the long winded approach:

PARAMETERS season_requested Text ( 255 );
SELECT [T\RAB Data].Species, [T\RAB Data].Date
FROM [T\RAB Data]
WHERE [T\RAB Data].Species='species name'
AND 
(Day([date]) Between (select [start day 1] from [season boundaries] where season = [season_requested]) And (select [end day 1] from [season boundaries] where season = [season_requested]) and
Month([date]) Between (select [start month 1] from [season boundaries] where season = [season_requested]) And (select [end month 1] from [season boundaries] where season = [season_requested])
OR
Day([date]) Between (select [start day 2] from [season boundaries] where season = [season_requested]) And (select [end day 2] from [season boundaries] where season = [season_requested]) and
Month([date]) Between (select [start month 2] from [season boundaries] where season = [season_requested]) And (select [end month 2] from [season boundaries] where season = [season_requested])
OR
Day([date]) Between (select [start day 3] from [season boundaries] where season = [season_requested]) And (select [end day 3] from [season boundaries] where season = [season_requested]) and
Month([date]) Between (select [start month 3] from [season boundaries] where season = [season_requested]) And (select [end month 3] from [season boundaries] where season = [season_requested]));

but as you see its unwieldy and I'm disappearing up my ... well you know where!

Is there a better way?

Rgds, Phil

[Jerry Winston]

This sounds feasible. Can you supply sample data/table structure?

BTW, do you know if your Date fields stored as text?

[Phil Davis]

Hi Jerry, many thanks for getting back on this.

the "Season Boundaries" table I've defined as:

Season - Text
Start Day 1 - Number
Start Month 1 - Number
End Day 1 - Number
End Month 1 - Number
repeat the above Number fields for Day 2 Month 2 Day 3 Month 3

The Date field from the "T\RAB Data" table is defined as Date/Time.

I define 4 "Seasons" - Summer, Winter, Combined and All in the Season Boundaries table.

Summer is defined as 1 4 31 7 0 0 0 0 0 0 0 0 so Summer starts on 1st April and ends 31st July
Winter is defined as 1 1 28 2 1 11 31 12 0 0 0 0 so Winter starts on 1st November and ends 28th February
Combined defined as 1 1 28 2 1 11 31 12 1 4 31 7 a combination of summer and winter
All defined as 1 1 31 12 0 0 0 0 0 0 0 0 gets me all records including those out of season

The query output is processed by VBA code where I originally performed the record filtering by converting dates to julian days which worked fine. However, I am being requested to bring this date processing into the query which makes some sense and has benefits for me also but its proving harder than I envisaged - I don't know enough advanced SQL and its difficult to find an exact example on the web - it sounded simple enough !!

I reckon, the SQL code I detailed in my first append will only work for all situations if I supply start day/month end day/month fields for each month in the season range (esp winter as it overlaps year boundaries).

I hope this is making sense!!

BTW where do Microsoft provide a comprehensive SQL Language Reference for Access? I see lots of blog entries that complain of the difficulty of finding this "first principles" info. Or do you have to buy a book? Because MSDN provide comprehensive VB and VBA documentation online I assumed there would be an SQL equivalent.

Kind Rgds, Phil

[Phil Davis]

Jerry, I think I may have answered my own question to a certain extent. Writing my last append prompted me to think about an SQL method of determining a Julian Date or day number for the year from the date in the record and season baoundaries table.

I found the DATEPART function which streamlines the processing for me.

I now defined Start Date 1/End Date 1 Start Date 2/End Date 2 etc as Date/Time in the "Season Boundaries" tables and code the where clause thus:

WHERE ((([T\RAB Data].Date) Between [Date1] And [Date 2])
AND
((DatePart("Y",[date])) Between (select datepart("Y",[start date 1]) from [season boundaries] where season = [season_requeste d]) And (select datepart("Y",[end date 1]) from [season boundaries] where season = [season_requeste d])
Or
(DatePart("Y",[date])) Between (select datepart("Y",[start date 2]) from [season boundaries] where season = [season_requeste d]) And (select datepart("Y",[end date 2]) from [season boundaries] where season = [season_requeste d])
Or
(DatePart("Y",[date])) Between (select datepart("Y",[start date 3]) from [season boundaries] where season = [season_requeste d]) And (select datepart("Y",[end date 3]) from [season boundaries] where season = [season_requeste d])))

This works although the performance is noticeably degraded and its still rather inelegant but I think I can run with it.

If you do have a better way I'd be interested.

Many thanks, Phil

[Phil Davis]

Have now added extra code to cater for leap year requirements. Here is the entire Query which extracts counts of bird populations by season and where the seasons are defined in a separate table with three date ranges specified for the year 2000 (a leap year). Hopefully this gives something back to this excellent forum. Can it be bettered?

 PARAMETERS Species_Requested Text ( 255 ), Date1 DateTime, Date2 DateTime, Season_Requested Text ( 255 );
SELECT Max([T\RAB Data].Meancount) AS Largest_Count, [T\RAB Data].Species AS SpName, [T\RAB Data].Square, [T\RAB Data].Tetrad, [T\RAB Data].Speccode AS SpCode
FROM [T\RAB Data]
WHERE ((([T\RAB Data].Date) Between [Date1] And [Date2]) 
AND   
(iif(datepart("Y",[date]) > 59, DatePart("Y",[date])+(29-Day(DateSerial(Year([date]),3,0))), DatePart("Y",[date])) Between (select datepart("Y",[start date 1])  from [season boundaries] where season = [season_requested]) And 
(select datepart("Y",[end date 1])  from [season boundaries] where season = [season_requested])
or 
iif(datepart("Y",[date]) > 59, DatePart("Y",[date])+(29-Day(DateSerial(Year([date]),3,0))), DatePart("Y",[date])) Between (select datepart("Y",[start date 2])  from [season boundaries] where season = [season_requested]) And 
(select datepart("Y",[end date 2])  from [season boundaries] where season = [season_requested])
or 
iif(datepart("Y",[date]) > 59, DatePart("Y",[date])+(29-Day(DateSerial(Year([date]),3,0))), DatePart("Y",[date])) Between (select datepart("Y",[start date 3])  from [season boundaries] where season = [season_requested]) And 
(select datepart("Y",[end date 3])  from [season boundaries] where season = [season_requested])))
GROUP BY [T\RAB Data].Species, [T\RAB Data].Square, [T\RAB Data].Tetrad, [T\RAB Data].Speccode
HAVING ((([T\RAB Data].Species) Like [Species_Requested] & "*"))
ORDER BY Max([T\RAB Data].Meancount), [T\RAB Data].Species, [T\RAB Data].Square, [T\RAB Data].Tetrad;