Solved

Date Comparisons

Posted on 2006-06-13
10
641 Views
Last Modified: 2012-08-13
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.


0
Comment
Question by:radhakrishan
10 Comments
 
LVL 7

Expert Comment

by:pradeepsudharsan
ID: 16892591
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
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 16892661
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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16892663
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))
)
0
 
LVL 11

Expert Comment

by:deroby
ID: 16893256
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16893464
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...


0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:radhakrishan
ID: 16893995
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.

0
 
LVL 11

Expert Comment

by:deroby
ID: 16894158
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 =)
0
 

Author Comment

by:radhakrishan
ID: 16894654
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...


 
0
 
LVL 11

Accepted Solution

by:
deroby earned 400 total points
ID: 16900943
Did some find&replace operations with those values and indeed, there seems to be a (big) mistake in my WHERE clause :
Seems I mixed up < and > on the first 2 lines ... try this :


 AND (
           (dateto <= @dateto  AND @dateto  <= dateend) -- requested period starts inside of an existing period
        OR (dateto <= @dateend AND @dateend <= dateend) -- 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 period completely overlaps an existing periodf
        )
       

Damn... should've tested, sorry!
0
 

Author Comment

by:radhakrishan
ID: 16909823
Deroby,

Works perfect!!

Cheers
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 46
Convert int to military time 8 20
Convert SP in a format for debugging 7 13
Syntax using Declare 3 11
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now