table structure :

CREATE TABLE [dbo].[Orders](

[Id] [int] NULL,

[DateofOrd] [datetime] NULL

)

Table data:

Id DateofOrd

12013-04-17 13:00:41.847

22013-04-17 14:00:41.847

32013-04-17 15:00:41.847

42013-04-17 15:10:41.847

52013-04-17 16:10:41.847

;WITH ordercte(id,timevalue)as

(

select id,ltrim(right(convert(nvarchar(20),DateofOrd,0),8)) as timevalue from orders

)

select * from ordercte where timevalue between '2:00PM' AND '3:10PM'

Result:

Id Timevalue

22:00PM

33:00PM

43:10PM