Tricky SQL Problem

See my answer in this thread http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=580096&SiteID=17

something like this work for your current design, however as I mentioned in the other thread you should seriously consider changing your schema

SELECT ID, YearID, MonthID, ProductName

FROM Products

WHERE YEARID BETWEEN @StartYear AND @EndYear

AND 1 = CASE WHEN @StartYear = YEARID AND MonthID < @StartMonth THEN 0

WHEN @EndYear = YEARID AND MonthID > @EndMonth THEN 0

ELSE 1 END

Change the @StartYear to $startYEar

DavidLundell at 2007-8-30 >

First off, format your query so you can see what it is doing:

SELECT *
FROM appointments
WHERE start_year BETWEEN $start_year AND $end_year
AND start_month >= $start_month AND end_month <= $end_month
AND start_day >= $start_day
ORDER BY start_year, start_month, start_day, start_time ASC

Then realize (as others have stated) that what you are trying to do does nt really make much sense. You are treating year, month, and day as if they are independent things. and they certainly are not. One of the reasons that date and time are part of a single value in SQL is that these are dependent upon one another. A case can be made to split off time, but generally only if you are interested in looking at things at the day level. Time is a hierarchy:

Year / Month / Day / Hour / Minute / etc.

So when you say you want to see everything in a year, that makes sense. But, when you ask:

Everything from 2005 to 2006 and January to February:

it is clear what the problem is. Independently you have said that the following matches:

2005 January, 2005 February (which you intended no doubt) AND 2006 January, 2006 February (which you would not have intended)

This is especially true when working with "Events" like appointments. You want to know the "relatively" exact date and time of the appointment, so you need to specifiy the complete starting point, and complete ending point.

So, to reiterate what has already been stated:

LouisDavidson at 2007-8-30 >