I don't understand what using the day itself would gain.

>May be you should try to take DAY of date from Table1 and see if it is in the date range?
>
>The way it's currently written you have it too complex and it may result in cartesian product.
>
>That's just a general idea, I bet this kind of problem was already discussed, I made a quick search here, but didn't find anything too relevant.
>
>>I am trying to fine tune a SPROC that is taking about 25 seconds to run.
>>
>>I have one table with a record every day. I have a second table with a record for a time frame, and it has a StartDate and EndDate field.
>>
>>I want to link the tables together in one query. I have been doing something like this:
>>
>>

>>SELECT
>>     Table1.Table1ID
>>     , Table1.Field1
>>     , Table1.Field2
>>     , Table1.EffectiveDate
>>     , Table2.Table2ID
>>     , Table2.Field1
>>FROM
>>     Table1
>>          LEFT OUTER JOIN Table2 ON (Table1.EffectiveDate > Table2.StartDate AND Table1.EffectiveDate < Table2.EndDate)
>>

>>
>>This is getting me the results I want, but at a performance price. Is there a better way of doing this, like loading Table2 into a temp table before joining? Thanks!