[sql] How can I compare time in SQL Server?

I'm trying to compare time in a datetime field in a SQL query, but I don't know if 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!

below query gives you time of the date

select DateAdd(day,-DateDiff(day,0,YourDateTime),YourDateTime) As NewTime from Table

TL;DR

Separate the time value from the date value if you want to use indexes in your search (you probably should, for performance). You can: (1) use function-based indexes or (2) create a new column for time only, index this column and use it in you SELECT clause.

Keep in mind you will lose any index performance boost if you use functions in a SQL's WHERE clause, the engine has to do a scan search. Just run your query with EXPLAIN SELECT... to confirm this. This happens because the engine has to process EVERY value in the field for EACH comparison, and the converted value is not indexed.

Most answers say to use float(), convert(), cast(), addtime(), etc.. Again, your database won't use indexes if you do this. For small tables that may be OK.

It is OK to use functions in WHERE params though (where field = func(value)), because you won't be changing EACH field's value in the table.

In case you want to keep use of indexes, you can create a function-based index for the time value. The proper way to do this (and support for it) may depend on your database engine. Another option is adding a column to store only the time value and index this column, but try the former approach first.

Edit 06-02

Do some performance tests before updating your database to have a new time column or whatever to make use of indexes. In my tests, I found out the performance boost was minimal (when I could see some improvement) and wouldn't be worth the trouble and overhead of adding a new index.

You can create a two variables of datetime, and set only hour of date that your need to compare.

declare  @date1 datetime;
declare  @date2 datetime;

select   @date1 = CONVERT(varchar(20),CONVERT(datetime, '2011-02-11 08:00:00'), 114)
select   @date2 = CONVERT(varchar(20),GETDATE(), 114)

The date will be "1900-01-01" you can compare it

if @date1 <= @date2
   print '@date1 less then @date2'
else
   print '@date1 more then @date2'

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)

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()

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

if (cast('2012-06-20 23:49:14.363' as time) between 
    cast('2012-06-20 23:49:14.363' as time) and 
    cast('2012-06-20 23:49:14.363' as time))

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

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)

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.

Using float does not work.

DECLARE @t1 datetime, @t2 datetime
SELECT @t1 = '19000101 23:55:00', @t2 = '20001102 23:55:00'
SELECT CAST(@t1 as float) - floor(CAST(@t1 as float)), CAST(@t2 as float) - floor(CAST(@t2 as float))

You'll see that the values are not the same (SQL Server 2005). I wanted to use this method to check for times around midnight (the full method has more detail) in which I was comparing the current time for being between 23:55:00 and 00:05:00.

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

Reference is here:

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

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

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.

@ronmurp raises a valid concern - the cast/floor approach returns different values for the same time. Along the lines of the answer by @littlechris and for a more general solution that solves for times that have a minute, seconds, milliseconds component, you could use this function to count the number of milliseconds from the start of the day.

Create Function [dbo].[MsFromStartOfDay] ( @DateTime datetime )
Returns int
As
  Begin
      Return (
               ( Datepart( ms , @DateTime ) ) +
               ( Datepart( ss , @DateTime ) * 1000 ) +
               ( Datepart( mi , @DateTime ) * 1000 * 60 ) +
               ( Datepart( hh , @DateTime ) * 1000 * 60 * 60 )
              )
  End

I've verified that it returns the same int for two different dates with the same time

declare @first datetime
set @first = '1900-01-01 23:59:39.090'
declare @second datetime
set @second = '2000-11-02 23:56:39.090'
Select dbo.MsFromStartOfDay( @first )
Select dbo.MsFromStartOfDay( @second )

This solution doesn't always return the int you would expect. For example, try the below in SQL 2005, it returns an int ending in '557' instead of '556'.

set @first = '1900-01-01 23:59:39.556'
set @second = '2000-11-02 23:56:39.556'

I think this has to do with the nature of DateTime stored as float. You can still compare the two number, though. And when I used this approach on a "real" dataset of DateTime captured in .NET using DateTime.Now() and stored in SQL, I found that the calculations were accurate.

Just change convert datetime to time that should do the trick:

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