>>>>Can not yet make it to work. That's my latest attempt which blows up in run-time:
>>>>
>>>>

>>>> var startTime = new DateTime(1900, 1, 1, beginDateTime.Hour, beginDateTime.Minute, 0);
>>>>                var endTime = new DateTime(1900, 1, 1, endDateTime.Hour, endDateTime.Minute, 0);
>>>>                var daysOfWeek = dailyLimits.Where(dl => dl.Selected == true).Select(ds => ds.WeekDay).ToList();
>>>>                if (daysOfWeek.Count() < 7) // not all days of the week selected
>>>>                {
>>>>                    query = query.Where(m4s => DateTime.Parse("01-01-1900 " + m4s.StartTime.Value.ToShortTimeString()) <= endTime &&
>>>>                    DateTime.Parse("01-01-1900 " + m4s.EndTime.Value.ToShortTimeString()) >= startTime && daysOfWeek.Contains((short)m4s.StartTime.Value.DayOfWeek));
>>>>                }

>>>>
>>>>about the Parse problem. Has anyone deal with this problem already of getting time portion of the date using LINQ?
>>>>
>>>>Thanks in advance.
>>>>
>>>>UPDATE. Tried TimeOfDay and it worked in my quick test, but the code it produced is not a clear conversion to time as I hoped. Still looking for better solutions.
>>>
>>>Don't use DateTime.Parse to add a date and time. Its a needless conversion and subject to regional settings. Create a DateTime object set to 1/1/1900 to use as your starting point. Add the StartTime or EndTime's TimeOfDay to your 1/1/1900 object's Date property to get the DateTime you are looking for.
>>
>>Thanks, Rob. The resulting SQL is still too complicated, e.g. here is the SQL I got in LINQPad:
>>
>>

>>-- Region Parameters
>>DECLARE @p0 Int = 1
>>DECLARE @p1 Int = 2
>>DECLARE @p2 DateTime = '1900-01-01 00:00:00.000'
>>DECLARE @p3 DateTime = '1900-01-01 20:00:00.000'
>>DECLARE @p4 DateTime = '1900-01-01 00:00:00.000'
>>DECLARE @p5 DateTime = '1900-01-01 08:00:00.000'
>>-- EndRegion
>>SELECT TOP (300) [t0].[id] AS [Id], [t0].[type] AS [Type], [t0].[start_time] AS [Start_time], [t0].[end_time] AS [End_time]
>>FROM [max4sale] AS [t0]
>>WHERE ((CONVERT(Int,(DATEPART(dw, [t0].[start_time]) + (@@DATEFIRST) + 6) % 7)) IN (@p0, @p1)) AND (DATEADD(ms, ((CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[start_time]))) * 36000000000) + 
>>((CONVERT(BigInt,DATEPART(MINUTE, [t0].[start_time]))) * 600000000) + 
>>((CONVERT(BigInt,DATEPART(SECOND, [t0].[start_time]))) * 10000000) + ((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[start_time]))) * 10000))) / 10000) % 86400000, 
>>DATEADD(day, (CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[start_time]))) * 36000000000) + 
>>((CONVERT(BigInt,DATEPART(MINUTE, [t0].[start_time]))) * 600000000) + 
>>((CONVERT(BigInt,DATEPART(SECOND, [t0].[start_time]))) * 10000000) + 
>>((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[start_time]))) * 10000))) / 864000000000, @p2)) <= @p3) AND (DATEADD(ms, ((CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[end_time]))) * 36000000000) + 
>>((CONVERT(BigInt,DATEPART(MINUTE, [t0].[end_time]))) * 600000000) + 
>>((CONVERT(BigInt,DATEPART(SECOND, [t0].[end_time]))) * 10000000) + 
>>((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[end_time]))) * 10000))) / 10000) % 86400000, DATEADD(day, (CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[end_time]))) * 36000000000) + 
>>((CONVERT(BigInt,DATEPART(MINUTE, [t0].[end_time]))) * 600000000) + 
>>((CONVERT(BigInt,DATEPART(SECOND, [t0].[end_time]))) * 10000000) + ((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[end_time]))) * 10000))) / 864000000000, @p4)) >= @p5)
>>

>>
>>I think it's time to convert the above logic into SP. Part of the reason I didn't want to do so is that I construct query dynamically depending on the type. I would need to reproduce the same logic in SP then. But the above code looks too monstrous to me.
>
>You can probably simplify it by getting rid of the date portion altogether:
>

>var startTime = new TimeSpan(beginDateTime.Hour, beginDateTime.Minute, 0); // Or maybe just take beginDateTime.TimeOfDay?
>var endTime = new TimeSpan(endDateTime.Hour, endDateTime.Minute, 0);
>var daysOfWeek = dailyLimits.Where(dl => dl.Selected == true).Select(ds => ds.WeekDay).ToList();
>if (daysOfWeek.Count() < 7) // not all days of the week selected
>{
>     query = query.Where(m4s => m4s.StartTime.Value.TimeOfDay <= endTime &&
>     m4s.EndTime.Value.TimeOfDay >= startTime && daysOfWeek.Contains((short)m4s.StartTime.Value.DayOfWeek));
>}

>
>That may remove some of the convert/dateparts, since you don't need to add them anymore.

Sure, I got rid of the extra conversion already. Still, SQL Server TimeOfDay is quite complex. Right now I'm testing this query and looking at it in the profile. Will then test separately in SSMS and check execution plan. My hope that these extra conditions do not make the query too slow as the main check by the date ranges.