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

  • What does exec sp_updatestats do?
  • Strange Behaviour in Recursive Hierarchy in SSRS
  • full text search stored procedure get no results although relevant data exists
  • Week and month sort
  • how to insert subtract of each two subsequent rows and inserting it into a new column
  • Pattern for normalized tables
  • Connection Pooling in .NET/SQL Server?
  • SQL Server connection string problems
  • How to delete xml node from ntext column in SQL
  • Getting output of MS stored proc on php call (sqlsrv)
  • SQL procedure return codes when column mismatch occurs on INSERT statement
  • SQL: transform full-text search into like construction
  • One Solution collect form web for “How to filter a table by T_SQL in SQL Server 2005, using a specific time for a column of “DateTime” datatype”

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.