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.
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.
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,@datee nd)>=0
and
datediff(day,,@dateto,date end)>=0
GO
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,@datee
and
datediff(day,,@dateto,date
GO
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))
)
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, 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...
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...
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 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 =)
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 =)
ASKER
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...
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Deroby,
Works perfect!!
Cheers
Works perfect!!
Cheers
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