Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date Comparisons

Posted on 2006-06-13
10
Medium Priority
?
648 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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 1600 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 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