SQL statement to find if dates is between two dates

Question

With a SQL statement, I want to find the entries for a single day.

Consider the following entries:

• Id 1, StartAt 2020-04-12 08:00:00.0000000, EndAt 2020-04-12 15:00:00.0000000
• Id 2, StartAt 2020-04-11 08:00:00.0000000, EndAt 2020-04-13 15:00:00.0000000

When I want to find the entries for the day:

• 2020-04-11, result should be: Id 2
• 2020-04-12, result should be: Id 1 and 2
• 2020-04-13, result should be: Id 2

I tried using different '<' '>' operators and the BETWEEN keyword, but couldn't get the right syntax, as it's a timeperiode I want to find, within another timeperiode.

Answer 1

I think you want:

where @date <= endAt and
      dateadd(day, 1, @date) > startat

This returns any timespan that includes any time on the @date.

Answer 2

Consider:

select count(*)
from mytable
where 
    @mydate >= cast(startAt as date) 
    and @mydate < dateadd(day, 1, cast(endAt as date))

Demo on DB Fiddle - you can play around with the value of @mydate to test the 3 scenarios.