Link to home
Start Free TrialLog in
Avatar of tim_carter
tim_carter

asked on

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
Avatar of matrix_aash
matrix_aash
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of tim_carter
tim_carter

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
But i have to check both the EndDate, and the StartDate?
Well, I would think that you would want to see if some date falls within a date range for the same user to see if the meeting would overlap.