Luis is absolutely correct about datatypes here. You are using sql 2008 so you have the time datatype. This would be the appropriate time (pun intended) to use that datatype.

CREATE TABLE [dbo].[Schedule]

(

[id] [int] IDENTITY(1,1) NOT NULL,

[weekdayid] [int] NULL,

[starttime] time NULL,

[endtime] time NULL

)

Now that you have a datatype you can do some calculations with this becomes simple.

declare @ParameterWithCorrectDatatype time = '10:00am'

if exists(select * from Schedule where @ParameterWithCorrectDatatype >= starttime and @ParameterWithCorrectDatatype <= endtime)

select 'The parameter is valid.'

else

select 'The parameter is invalid.'

I say the incorrect data type here because I would make the parameter's datatype also be time.

You can kludge the same thing using varchar however there are a couple of issues with that.

1) It will not perform as well because of all the datatype conversion required.

2) If you have ANY row in the table that is not able to be cast as time it will fail.

Here is what the same code would look using varchar instead of time.

declare @ParameterWithIncorrectDatatype varchar(10) = '10:00am'

if exists(select * from Schedule where cast(@ParameterWithIncorrectDatatype as time) >= cast(starttime as time) and cast(@ParameterWithIncorrectDatatype as time) <= cast(endtime as time))

select 'The parameter is valid.'

else

select 'The parameter is invalid.'

Hope that helps.