how to check if there data range between 2 given dates?

Question

I have start time and end time in my application. I now need to check if there is any time falling between those time in SQL Query.

How do I write the query.

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 to not use between when comparing datetime. The result is often not what you want.

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

Test data for demo

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

Query using 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 < instead.

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 hints at that you want to check for overlapping date intervals. If that is the case you can have a look at this question How can I determine in SQL Server if a dateTime range overlaps another.