How can I compare time in SQL Server?

Question

I am trying to compare the time in a datetime field in an SQL query, but I do not know how it is right. I do not want to compare the date, part of the time.

I'm doing it:

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

Is it correct?

I ask about this because I need to know if '08: 00: 00 'is less or more than '07: 30: 00', and I don't want to compare the date, only part of the time.

Thank!

Answer 1

Your comparison will work, but it will be slow because the dates are converted to a string for each row. To effectively compare two parts of the time, 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: the date on the SQL server is stored as a floating point number. The digits before the decimal point represent the date. The numbers after the decimal point represent time.

So here is an example of a date:

 declare @mydate datetime set @mydate = '2009-04-30 19:47:16.123' 

Let me convert it to float:

 declare @myfloat float set @myfloat = cast(@mydate as float) select @myfloat -- Shows 39931,8244921682 

Now take the part after the number, i.e. time:

 set @myfloat = @myfloat - floor(@myfloat) select @myfloat -- Shows 0,824492168212601 

Convert it back to datetime:

 declare @mytime datetime set @mytime = convert(datetime,@myfloat) select @mytime -- Shows 1900-01-01 19:47:16.123 

1900-01-01 is only a “zero” date; you can display the temporary part with convert, specifying, for example, format 108, which is only time:

 select convert(varchar(32),@mytime,108) -- Shows 19:47:16 

Conversions between datetime and float are pretty fast, because they are basically stored the same way.

Answer 2

 convert(varchar(5), thedate, 108) between @leftTime and @rightTime 

Explanation:

if you have varchar(5) you will get HH:mm

if you have varchar(8) you get HH:mm ss

108 only gets time from SQL date

@leftTime and @rightTime are two variables to compare

Answer 3

If you are using SQL Server 2008, you can do this:

 WHERE CONVERT(time(0), startHour) >= CONVERT(time(0), @startTime) 

Here's the 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 4

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

Answer 5

One (possibly small) problem that I already spoke about with solutions is that they all require a function call to handle the comparison. This means that the query engine will have to do a full table scan to find the rows you are after - and you won’t be able to use the index. If the table is not particularly large, it probably will not have any negative consequences (and you can gladly ignore this answer).

If, on the other hand, the table can become quite large, query performance may suffer.

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

If, however, the datetime column is used to store both the date and time of the event, this obviously will not work. In this case, if you can change the application and the table structure, divide the date and time into two separate datetime columns or create an indexed view that will select all (corresponding) columns of the original table, as well as the next column containing which you want to find (use any of the previous answers to calculate this) - and modify the application to request a view.

Answer 6

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 will see that the values ​​do not match (SQL Server 2005). I wanted to use this method to check the time around midnight (the full method has more detailed information), in which I compared the current time between 23:55:00 and 00:05:00.

Answer 7

Use Datepart function: DATEPART (datepart, date)

eg #

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

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

You can use DateDiff if your dates are the same, otherwise you will need to delete the date above

Answer 8

Adding to other answers:

you can create a function to trim dates from datetime

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

So this is:

 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 9

Just change the conversion date and time to do the trick:

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

Answer 10

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

Link here:

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

Answer 11

I don’t like to rely on the insides of the repository (this is the date and time - this is a float with integer = day and fractional = time), but I do the same as Johnny D. Kano's answer. That is how I know all db developers. Definitely not convert to string. If you shouldn't handle it like float / int, then the best option is to pull an hour / minute / second / millisecond using DatePart ()

Answer 12

You can create two datetime variables and set only the hour of the date you 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' 

Answer 13

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

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

Answer 14

I assume your startHour column and @startHour variable are DATETIME; In this case, you should convert the string:

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

Answer 15

below request gives you date time

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

Answer 16

@ronmurp is a serious concern - the cast / floor approach returns different values ​​at the same time. According to @littlechris answer and for a more general solution that resolves in a few minutes, seconds, a millisecond component, you can use this function to count the number of milliseconds since the beginning 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 checked 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 does not always return the int you expect. For example, try 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 is due to the fact that DateTime is stored as a float. However, you can compare two numbers. And when I used this approach in a “real” DateTime dataset written in .NET using DateTime.Now () and stored in SQL, I found that the calculations were accurate.