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

  • Concatenating records in a single column without looping?
  • A good tool to populate database with dummy data?
  • sql select 3 columns and dedupe on two columns
  • Wrong case in subquery column name causes incorrect results, but no error
  • Exposing SQL Server database objects as files in a file system
  • SQL Server : view column datatype does not change when underlying table datatype is changed?
  • Solution to avoid double-hop from client > web service > SQL Server
  • Unique constraint on two columns regardless of order
  • Case sensitivity and database projects
  • Execute SQL Stored procedure in Classic ASP
  • Generate scripts with new ids (also for dependencies)
  • Rolling back and update command in Sql Server 2000
  • 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.