How can I compare time in SQL Server?

Question

Hi!

I'm trying to compare time in a datetime field in a SQL query, but I don't know it it's right. I don't want to compare the date part, just the time part.

I'm doing this:

SELECT timeEvent FROM tbEvents WHERE convert(datetime, startHour, 8) >= convert(datetime, @startHour, 8)

Is it correct?

I'm asking this because I need to know if '08:00:00' is less or greater than '07:30:00' and I don't want to compare the date, just the time part.

Thanks!

Answer 1

What is startHour and @startTime?

Are they just ints containing the hour?

If it is then why do you need to convert them to a date in order to check one is larger than the other?

I think you probably need to provide some more information. Your table structure and a sample of the data you have in it would help a lot.

Answer 2

Hi,

Use Datepart function: DATEPART(datepart, date)

E.g#

SELECT DatePart(@YourVar, hh)*60) + DatePart(@YourVar, mi)*60)

This will give you total time of day in minutes allowing you to compare more easily.

You can use DateDiff if your dates are going to be the same, otherwise you'll need to strip out the date as above

Answer 3

SELECT timeEvent 
  FROM tbEvents 
 WHERE CONVERT(VARCHAR,startHour,108) >= '01:01:01'

This tells SQL Server to convert the current date/time into a varchar using style 108, which is "hh:mm:ss". You can also replace '01:01:01' which another convert if necessary.

Answer 4

I believe you want to use DATEPART('hour', datetime).

Reference is here:

http://msdn.microsoft.com/en-us/library/ms174420.aspx

Answer 5

I am assuming your startHour column and @startHour variable are both DATETIME; In that case, you should be converting to a string:

SELECT timeEvent
FROM tbEvents
WHERE convert(VARCHAR(8), startHour, 8) >= convert(VARCHAR(8), @startHour, 8)

Answer 6

Adding to the other answers:

you can create a function for trimming the date from a datetime

CREATE FUNCTION dbo.f_trimdate (@dat datetime) RETURNS DATETIME AS BEGIN
    RETURN CONVERT(DATETIME, CONVERT(FLOAT, @dat) - CONVERT(INT, @dat))
END

So this:

DECLARE @dat DATETIME
SELECT @dat = '20080201 02:25:46.000'
SELECT dbo.f_trimdate(@dat)

Will return 1900-01-01 02:25:46.000

Answer 7

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)

Answer 8

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

Answer 9

Why do I have to convert to VARCHAR so I can compare if a time is greater than the other one? Would't I this way comparing string with string??

Answer 10

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 digit, 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.

Answer 11

I don't love relying on storage internals (that datetime is a float with whole number = day and fractional = time), but I do the same thing as the answer Jhonny D. Cano. This is the way all of the db devs I know do it. Definitely do not convert to string. If you must avoid processing as float/int, then the best option is to pull out hour/minute/second/milliseconds with DatePart()

Answer 12

One (possibly small) issue I have noted with the solutions so far is that they all seem to require a function call to process the comparison. This means that the query engine will need to do a full table scan to seek the rows you are after - and be unable to use an index. If the table is not going to get particularly large, this probably won't have any adverse affects (and you can happily ignore this answer).

If, on the other hand, the table could get quite large, the performance of the query could suffer.

I know you stated that you do not wish to compare the date part - but is there an actual date being stored in the datetime column, or are you using it to store only the time? If the latter, you can use a simple comparison operator, and this will reduce both CPU usage, and allow the query engine to use statistics and indexes (if present) to optimise the query.

If, however, the datetime column is being used to store both the date and time of the event, this obviously won't work. In this case if you can modify the app and the table structure, separate the date and time into two separate datetime columns, or create a indexed view that selects all the (relevant) columns of the source table, and a further column that contains the time element you wish to search for (use any of the previous answers to compute this) - and alter the app to query the view instead.