How can I compare time in SQL Server?
convert(varchar(5), thedate, 108) between @leftTime and @rightTime
Explanation:
if you have varchar(5)
you will obtain HH:mm
if you have varchar(8)
you obtain HH:mm ss
108 obtains only the time from the SQL date
@leftTime
and @rightTime
are two variables to compare
Your compare will work, but it will be slow because the dates are converted to a string for each row. To efficiently compare two time parts, try:
declare @first datetime
set @first = '2009-04-30 19:47:16.123'
declare @second datetime
set @second = '2009-04-10 19:47:16.123'
select (cast(@first as float) - floor(cast(@first as float))) -
(cast(@second as float) - floor(cast(@second as float)))
as Difference
Long explanation: a date in SQL server is stored as a floating point number. The digits before the decimal point represent the date. The digits after the decimal point represent the time.
So here's an example date:
declare @mydate datetime
set @mydate = '2009-04-30 19:47:16.123'
Let's convert it to a float:
declare @myfloat float
set @myfloat = cast(@mydate as float)
select @myfloat
-- Shows 39931,8244921682
Now take the part after the comma character, i.e. the time:
set @myfloat = @myfloat - floor(@myfloat)
select @myfloat
-- Shows 0,824492168212601
Convert it back to a datetime:
declare @mytime datetime
set @mytime = convert(datetime,@myfloat)
select @mytime
-- Shows 1900-01-01 19:47:16.123
The 1900-01-01 is just the "zero" date; you can display the time part with convert, specifying for example format 108, which is just the time:
select convert(varchar(32),@mytime,108)
-- Shows 19:47:16
Conversions between datetime and float are pretty fast, because they're basically stored in the same way.
Just change convert datetime to time that should do the trick:
SELECT timeEvent
FROM tbEvents
WHERE convert(time, startHour) >= convert(time, @startHour)
If you're using SQL Server 2008, you can do this:
WHERE CONVERT(time(0), startHour) >= CONVERT(time(0), @startTime)
Here's a full test:
DECLARE @tbEvents TABLE (
timeEvent int IDENTITY,
startHour datetime
)
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 0, GETDATE())
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 1, GETDATE())
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 2, GETDATE())
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 3, GETDATE())
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 4, GETDATE())
INSERT INTO @tbEvents (startHour) SELECT DATEADD(hh, 5, GETDATE())
--SELECT * FROM @tbEvents
DECLARE @startTime datetime
SET @startTime = DATEADD(mi, 65, GETDATE())
SELECT
timeEvent,
CONVERT(time(0), startHour) AS 'startHour',
CONVERT(time(0), @startTime) AS '@startTime'
FROM @tbEvents
WHERE CONVERT(time(0), startHour) >= CONVERT(time(0), @startTime)