Compare time portion of datetime values in sql

Question

How to compare only the time portion of datetime values in sql.

I have tried it with the following way,

 DECLARE @t1  smalldatetime
 SET @t1=CAST('1900-01-01 '+CONVERT(varchar(8),@saledate,108) As smalldatetime) 
 --------------------------------------------------------------------------------

 SELECT Model FROM  Sales WHERE @t1 between WorkStart  and WorkEnd And Ctrl=@key

'WorkStart' and 'WorkEnd' is a DateTime field and the date portion always contains '1900-01-01'.

And the parameter @saledate is a datetime, how can i compare only the time portion without consider the date portion of @saledate,workstart and workend.

The table already created the WorkStart and WorkEnd with DateTime field and i cannot modify the field datatype as it is currently being used.

Solution

If you use SQL Server 2008 or newer, you can cast to TIME datatype to compare the time portion.

SELECT Model 
FROM Sales 
WHERE 
  CAST(@saledate AS time) BETWEEN CAST(WorkStart AS time) AND CAST(WorkEnd AS time) 
  AND Ctrl = @key

If you do that often, it would be more performant to have your times stored to database columns as TIME, not DATETIME.