CODEWITHSUNDEEP
sql-servert-sqldatetimetime
odiseh
odiseh

Reputation: 26547

How to filter a table by T_SQL in SQL Server 2005, using a specific time for a column of "DateTime" datatype

How to filter a table by T_SQL in SQL Server 2005, using a specific time for a column of "DateTime" datatype?

Thank you

Upvotes: 1

Views: 495

Answers (1)

AdaTheDev
AdaTheDev

Reputation: 147374

For example, to find all record with a time (irrespective of date) between 9AM and 11AM, you can do this:

SELECT * 
FROM YourTable 
WHERE CONVERT(VARCHAR(5), DateField, 114) BETWEEN '09:00' AND '11:00'

However, performance isn't likely to be great as any index on DateField would not be used. If this is something you're going to be doing frequently filtering on the time element, I'd suggest storing the time separate from the date part. That would make querying a lot more optimal.

In SQL Server 2008 there is actually a TIME datatype, so if you're heading towards 2008 at some point, then the separate field approach is even more something to consider.

Upvotes: 3

Related Questions