How to check if a data range exists between two given dates?

Question

My application has a start and end time. Now I need to check if there is some time between these points in SQL Query.

How to write a request.

thanks

Answer 1

This should work for you.

Select * From MyTable Where timecreated Between Cast('7/20/08 12:01:01' As DateTime) And Cast('7/20/09 12:01:01' as DateTime) 

Answer 2

I would recommend not using between when comparing date and time. The result is often not what you want.

BETWEEN returns TRUE if test_expression is greater than or equal to begin_expression and less than or equal to end_expression.

Demonstration Test Data

 declare @T table (dt datetime) insert into @T values('2011-04-12T09:00:00') insert into @T values('2011-04-12T10:00:00') insert into @T values('2011-04-12T11:00:00') insert into @T values('2011-04-12T12:00:00') 

Request between

 select * from @T where dt between '2011-04-12T10:00:00' and '2011-04-12T11:00:00' 

Result: 11:00 is included in the result.

 dt 2011-04-12 10:00:00.000 2011-04-12 11:00:00.000 

Rewrite the query using >= and < .

 select * from @T where dt >= '2011-04-12T10:00:00' and dt < '2011-04-12T11:00:00' 

Result

 dt 2011-04-12 10:00:00.000 

The title of the question suggests that you want to check the intervals between overlapping dates. If so, you can take a look at this question How can I determine in SQL Server if the dateTime range overlaps another .

Answer 3

This will also work: -

  select * from tablename where time between '7/7/2009 12:35:35' and '7/7/2010 23:35:35'