How To Select Rows From Table Based On Time Value in DateTime Column

Recently, I was tasked with retrieving only those records from a transaction table in which the transaction datetime timestamp column had a time value on or after 5:30 pm. Following is a query I came up with for doing such selects. The sample code uses the AdventureWorks sample database. I could not find any column in the database that had time values in datetime fields and ended up creating the sample data to test this query.

So before running this sample code, remember to run the update query to update 2 of the rows with time values, so that you can see that this query actually works. Also remember to change the datetime format number (Ex 101 for US dates) in the CONVERT methods in the SQL before using it in your code.

Update 2 Rows With Time value For Testing The Select Query

use AdventureWorks
go

-- update 2 rows with time to test the select query
update Sales.SalesTerritory set ModifiedDate = '1998-06-01 17:30:06.000' where TerritoryID = 2
update Sales.SalesTerritory set ModifiedDate = '1998-06-01 18:35:11.000' where TerritoryID = 3

Query To Select Rows Based On Time Only – US Date Format

use AdventureWorks
go

-- select only those territories whose modified time on 6/1/1998 was after 5:30 pm (date in US format)
declare @checkDateTime DATETIME
select @checkDateTime  = CONVERT(DateTime, CONVERT(Char, '6/1/1998', 101), 101) + convert(datetime, '17:30:00',114)
select ModifiedDate, * from Sales.SalesTerritory where  ModifiedDate  >= @checkDateTime

Query To Select Rows Based On Time Only – British / UK Date Format

use AdventureWorks
go

-- select only those territories whose modified time on 1/6/1998 was after 5:30 pm (date in our beloved queen's format)
declare @checkDateTime DATETIME
select @checkDateTime  = CONVERT(DateTime, CONVERT(Char, '1/6/1998', 103), 103) + convert(datetime, '17:30:00',114)
select ModifiedDate, * from Sales.SalesTerritory where  ModifiedDate  >= @checkDateTime

If you want to do it for the current date, replace the hardcoded date value in the example with GETDATE() Likewise, if you want to do it for yesterdays date, or the day before yesterday, use GETDATE() – 1 or GETDATE() – 2 respectively.