Link to home
Start Free TrialLog in
Avatar of radhakrishan
radhakrishan

asked on

Date Comparisons

Hi Guys,

I am designing an online booking application, that requires me to check wheather the slot selected by the user is available or not and i wrote the following store procedure.... Main idea behind is to check if the date slot overlaps with any other slot already booked.


if the value in counter is > 0 it means the slot is not available.....

create procedure spcheckbookings
@categoryid as integer,
@equipmentid as integer,
@dateto as datetime,
@dateend as datetime
as

Select count(*) as Counter
from tbBookings
where
(
(dateto = @dateto and dateend = @dateend)
or
(dateto > @dateto and dateend > @dateend and @dateto < dateend)
or
(dateto < @dateto and dateend < @dateend and dateend > @dateto)
)
GO

But the date comparisons does not give me right results for some reasons, and when we compare dates are time compared as well is
13-06-2006 10:12 > 13-06-2006 09:12

As i need to get this working too.

Any help much appreciated.


Avatar of pradeepsudharsan
pradeepsudharsan

Hi,
use DATEDIFF function
DATEDIFF ( datepart , startdate , enddate )

startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.


Regards
Pradeep
As pradeepsudharsan  mentioned, Try this!

create procedure spcheckbookings
@categoryid as integer,
@equipmentid as integer,
@dateto as datetime,
@dateend as datetime
as

Select count(*) as Counter
from tbBookings
where
datediff(day,dateto,@dateend)>=0
and
datediff(day,,@dateto,dateend)>=0
GO
Avatar of Aneesh
you can convert the datetime variables like this

SELECT convert (varchar(8),getdate(),112)

where
(
(SELECT convert (varchar(8),dateto,112)  = SELECT convert (varchar(8),@dateto,112) and SELECT convert (varchar(8),dateend,112)  = SELECT convert (varchar(8),@dateend,112))
or
(convert (varchar(8),dateto,112)  < SELECT convert (varchar(8),@dateto,112) and SELECT convert (varchar(8),dateend,112) > SELECT convert (varchar(8),@dateend,112) and SELECT convert (varchar(8),@dateto,112) < convert (varchar(8),dateend,112))
or
(convert (varchar(8),dateto,112)  > SELECT convert (varchar(8),@dateto,112) and SELECT convert (varchar(8),dateend,112) < SELECT convert (varchar(8),@dateend,112) and SELECT convert (varchar(8),dateend,112) > SELECT convert (varchar(8),@dateto,112))
)
aneeshattingal:

please, oh pleas don't 'advise' people to use convert(varchar, x, 112) to get rid of the time-part. This has been discussed quite a few times before and the end conclusion has each time been : DONT

=> What you're doing here is convert 2 entire column (might be big, database could hold millions of rows!) into strings, and then do a string comparison against two 'fixed' strings.

Sure it will work, but it will also throw out the use of any indexes you might have (Convert() makes this non-SARG-able). It will be fast on small tables, but will soon become a bottleneck once you go into the bigger numbers)

A simple solution to get rid of the time-part of a datetime is to litteraly substract the hour/minute/seconds/ms parts of the starting value, a lot more typing, but MUCH less work for the server

DECLARE @base_datetime datetime

SELECT @base_datetime = CURRENT_TIMESTAMP

SELECT  date_only =
DateAdd(hour, -1 * DatePart(hour, @base_datetime),
DateAdd(minute, -1 * DatePart(minute, @base_datetime),
DateAdd(second, -1 * DatePart(second, @base_datetime),
DateAdd(ms, -1 * DatePart(ms, @base_datetime), @base_datetime))))

Apart from that, MSSQL will not accept the syntax you used =(

My suggestion to the question asked would be to write it like this :


CREATE PROCEDURE spcheckbookings
                (@categoryid  as integer,
                 @equipmentid as integer,
                 @dateto      as datetime,
                 @dateend     as datetime)
AS

SELECT COUNT(*) AS Counter
  FROM tbBookings
 WHERE categoryid = @categoryid
   AND equipmentid = @equipmentid
   AND (
           (dateto > @dateto AND @dateto < datend) -- requested period starts inside of an existing period
        OR (dateto > @dateend AND @dateend < datend) -- requested period ends inside of an existing period
        )

This will indeed compare dates AND time-parts, but I'm not sure that that is what you want ... please clarify if you prefer to check on the date-part only. (if so, then why store time-parts in the first place ?)

ps: you might want to replace the greater than/smaller than signs with their 'or equals' counterparts (<= and >=)  when you would consider "matching" end- and start-dates too.
(eg. existing period : "1 jan 06 12:30 - 1 jan 06 14:30" and you want to check it against  "1 jan 06 14:30 - 1 jan 06 18:30". Current check will NOT return it, if you make it >= and <=, it will)

my 2 cents
please confirm what sort of datetime slots your trying to check...

give us some sample slots filled
and some search ranges that should and shouldn't work...

please also confirm how you are storing you slot booked periods...


Avatar of radhakrishan

ASKER

Hi guys,

I do need to compare time as well in the problem as suppose there is a row in a table like

Catgry          Datefrom                          Dateto
RoomA         29/05/2006 11:00:00         29/05/2006 12:00:00

Now if someone wants to book
Room A from

29/05/2006 10:00:00    29/05/2006 13:00:00 'Should b False
29/05/2006 11:00:00    29/05/2006 12:00:00 'Should b False
29/05/2006 12:00:00    29/05/2006 14:00:00 'Should b False

It should only be accepted if it does not overlap with the time period booking already present and that involves checking both date and time...


I have changed my SQL to

Select count(*) as counter
from tbBookings
where
(
(dateto >= @dateto and dateend <= @dateend)
or
(dateto >= @dateto and dateto<@dateend and dateend >= @dateend and dateend>@dateto))

Do you guys think it's the best way to deal with this problem, as I don't think DATEDIFF is applicable in this kind of problem.

I'm pretty sure that your approach is quite optimal (if not THE optimal) approach.
I'm not quite sure however wether you have all situations covered in your WHERE clause though ...

Regarding your examples: "should b false" comes down to 'count > 0' right ??

I only just realise I didn't account for two possibilities in my code ... change my example to

CREATE PROCEDURE spcheckbookings
                (@categoryid  as integer,
                 @equipmentid as integer,
                 @dateto      as datetime,
                 @dateend     as datetime)
AS

SELECT COUNT(*) AS Counter
  FROM tbBookings
 WHERE categoryid = @categoryid
   AND equipmentid = @equipmentid
   AND (
           (dateto >= @dateto  AND @dateto  <= datend) -- requested period starts inside of an existing period
        OR (dateto >= @dateend AND @dateend <= datend) -- requested period ends inside of an existing period
        OR (dateto <= @dateto  AND @dateend <= dateend) -- requested period falls completely inside an existing period
        OR (dateto >= @dateto  AND @dateend >= dateend) -- requested perod completely overlaps an existing periodf
        )


(hope it's right this time =)
Hi Deroby,


Cheers for the help, I think we are nearly there but come across another problem that it counts more then 1 row in scenario such as :

Consider two rows in table as


ROOM A   2006-05-29 11:00:00.000      2006-05-29 12:00:00.000

ROOM A  2006-06-01 11:30:00.000              2006-06-02 12:30:00.000

If I try to execute:

exec spcheckbookings 1, 1, '2006-05-29 10:00:00', '2006-05-29 11:00:00'

The number of row count is 2.... It should not consider the other row as there is no overlap watsoever...


 
ASKER CERTIFIED SOLUTION
Avatar of deroby
deroby

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
Deroby,

Works perfect!!

Cheers