# Counting Dates And TIME

Hi Guys

How come this returns a count of 1, when there is only a booking between 3:00 and 4:00. `?

SELECT Count(*) as Total FROM Booking WHERE CustomerId = 1 AND StartDate <= DATEADD(day, 1, CONVERT(datetime, '2008-07-23 01:00', 120) ) AND EndDate >= CONVERT(datetime, '2008-07-23 02:00', 120) AND UserId = 1 AND Deleted IS NULL
matrix_aash

You have mentioned the time between 1 and 2 and not 1 and 4. I hope i have understood the question.

your other condition might not be meeting the where clause?

Cheers,

Aash.
tim_carter

This select should return the count of 0 right? im asking in the range between 1 and 2. and the only thing in the database on that day is 3 to 4
chapmandew

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

i turned it the wrong way, sorry this works

SELECT
*
FROM
Booking
WHERE
CustomerId = 1 AND
StartDate <= DATEADD(day, 1, CONVERT(datetime, '2008-07-23 01:00:00', 120) )
AND EndDate <= CONVERT(datetime, '2008-07-23 03:40:00', 120)
AND UserId = 1
AND Deleted IS NULL

but i have another problem, this is for checking a booking before a users books something so they cant make a booking over another booking. But my problem is if i have a booking starting at 3:00 and ending at 4:00 then if i check the time 03:40 it doesnt return anything. how come that is? it is overlapping the other meeting?

Ahh i se my problem.. stupid.. Guys how do i check on a daterange a simple way?

lets say, i have a meeting from 2008-07-23 01:00 to 2008-07-23 03:00

then the user creates another meeting from 2008-07-23 00:00 to 2008-07-23 2:30

i dont wanna alow that? how do i query this? thanks for all your help
when creating the 2nd meeting, just make sure that there isn't an existence of that time range

select * from tablename t
where not exists(select 1 from tablename ti where t.user = ti.user and ti.timefield between t.timefield1 and t.timefield2)