How to Compare Time in SQL Server

How can I compare time in SQL Server?

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.

Compare time portion of datetime values in sql

If you use SQL Server 2008 or newer, you can cast to TIME datatype to compare the time portion.

SELECT Model 
FROM Sales 
WHERE 
  CAST(@saledate AS time) BETWEEN CAST(WorkStart AS time) AND CAST(WorkEnd AS time) 
  AND Ctrl = @key

If you do that often, it would be more performant to have your times stored to database columns as TIME, not DATETIME.

SQL datetime compare

Even though in Europe and everywhere in the world it makes perfect sense to use the month as the second of three items in the date. In the US and in this case, apparently, SQL's date time is MM.DD.YYYY, so you're going for the 15th month and 18th month

Therefore you should use

string query "SELECT * FROM LocalHotels WHERE city='LONDON' AND start <='12.15.2015 00:00:00' AND deadline >='12.18.2015 00:00:00' ORDER BY city"

or

string query "SELECT * FROM LocalHotels WHERE city='LONDON' AND start <='2015-12-15' AND deadline >='2015-12-18' ORDER BY city"

Compare time part of DateTime data type in SQL Server 2005

SELECT *
FROM Table1
WHERE DATEADD(day, -DATEDIFF(day, 0, MyDateField), MyDateField) > '12:30:50.400'

How to compare time in 24 hours format in sql server

You need to use datetime datatype and CASE WHEN to check if start date > end date to apply DATEADD to star date in case it is bigger then end date:

DECLARE @Start nvarchar(5)= N'16:30',
        @End nvarchar(5)= N'10:10',
        @Curr nvarchar(5)=N'09:30'

DECLARE @curdate nvarchar(9) = CONVERT(nvarchar(10),GETDATE(),112)+' '

DECLARE @starttime datetime = @curdate + @Start + ':00.000',
        @endtime datetime = @curdate + @End + ':00.000',
        @currtime datetime = @curdate + @Curr + ':00.000'

SELECT CASE WHEN @currtime between (
     CASE WHEN @starttime > @endtime 
            THEN DATEADD(day,-1,@starttime)
            ELSE @starttime END
                        )  and @endtime THEN 'SUCCESS' 
                                        ELSE 'FAIL' END as result

How can I compare time(7) in SQL Server?

You need to convert the date to time i.e

CAST(<YOUR DATE> AS time(7))

and to get rid of the ambiguous column you need to add a alias to your table as student_courses and courses both have courseId.

Without running it the problem looks like its here in the subquery

select **c.[Course ID]** from student_courses sc inner join Courses c on sc.[Course ID] = c.[Course ID]
    where [student ID]=@val1
    AND ((c.[Course start date] between Courses.[Course start date] and Courses.[Course end time])
    or (c.[Course end time] between Courses.[Course start time] and Courses.[Course end time]) 
    )

---

Related Topics