filter recordset by time of day (hour and minutes)

[emcguire]

I am using MS SQL server and I want to filter my recordset by the time of day. I want the record to display only between 10:20pm and 10:50pm each day. how can I do this . Thank You for any help!

[Claire]

insert into a select 1,getdate()
insert into a select 1,getdate()-1
insert into a select 1,getdate()-2
insert into a select 1,getdate()-3


select * from a where convert(varchar(20),tim,8) between '18:00:00' and '21:00:00'

[MAK]

create table mytime (id int, time datetime)

insert into mytime select 1,'02-22-2004 20:21:00'
insert into mytime select 1,'02-23-2004 23:00:00'
insert into mytime select 1,'02-22-2004 20:00:00'
insert into mytime select 1,'02-24-2004 12:00:00'
insert into mytime select 1,'02-24-2004 20:25:00'
insert into mytime select 1,'02-25-2004 11:00:00'
insert into mytime select 1,'02-27-2004 06:00:00'
insert into mytime select 1,'02-27-2004 20:35:00'
insert into mytime select 1,'02-27-2004 19:35:00'
insert into mytime select 1,'02-27-2004 20:15:00'
insert into mytime select 1,'02-24-2004 22:25:00'
insert into mytime select 1,'02-25-2004 22:45:00'
insert into mytime select 1,'02-26-2004 22:25:00'

select * from mytime where convert(varchar(20),time,8) between '22:20:00' and '22:50:00'

[Claire]

I dont see anything different between your solution and my solution.

[MAK]

requirement is between 10:20pm and 10:50pm

[emcguire]

You guys are Great!! Thank You both!