Check Whether A Date Lies Between Two Dates Using SQL


Hi

I have a given date, lets call it MyDate. I wish to check whether this date lies between two dates in a table using SQL like This:

"Select * From TableName Where MyDate Between CrStart And CrEnd".

TableName represents a Table of Bookings For A Conference Room. CrStart and CREnd Represent existing Bookings for a Specific conference room. If Mydate lies between CrStart and CREnd then the request should be rejected as "Conference Room Not Available".

I hope I am making myself clear. SQL does not like the Select Statement as it is.

Can somebody help me with this please.

Thanks.
NolancAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DhaestCommented:
Include the start and end date:

Select * From TableName Where MyDate >= CrStart And MyDate <= CrEnd

Exclude the start and end date

Select * From TableName Where MyDate > CrStart And MyDate < CrEnd
0
Ramkisan JagtapLead DeveloperCommented:
Your given statement is also correct statement, just check with your dates for the date format.
if the dates format for all the dates are not same then try to make it same and then use your statement.

"Select * From TableName Where MyDate Between CrStart And CrEnd"

regards,
Ram
0
bkokxCommented:
When using dates in a SQL Select note that for instance Feb 1, 2010 as text becomes #02/01/2010#
0
PrakashRaoBSCommented:
Try this..

Select * From TableName Where convert(varchar,MyDate,101) Between convert(varchar,CrStart,101) And convert(varchar,CrEnd,101)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NolancAuthor Commented:
Hi

Thanks for all your comntributions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.