Solved

Check Whether A Date Lies Between Two Dates Using SQL

Posted on 2010-08-16
5
803 Views
Last Modified: 2013-11-26

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.
0
Comment
Question by:Nolanc
5 Comments
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 30 total points
ID: 33443831
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
 
LVL 12

Assisted Solution

by:Ramkisan Jagtap
Ramkisan Jagtap earned 30 total points
ID: 33443984
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
 
LVL 3

Assisted Solution

by:bkokx
bkokx earned 30 total points
ID: 33444280
When using dates in a SQL Select note that for instance Feb 1, 2010 as text becomes #02/01/2010#
0
 
LVL 3

Accepted Solution

by:
PrakashRaoBS earned 35 total points
ID: 33444496
Try this..

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

Author Closing Comment

by:Nolanc
ID: 33445546
Hi

Thanks for all your comntributions.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Vb.net dynamic formulas in runtime 11 62
Word Directory is not in the drop down list 4 25
Runtime Exceptions when trying to submit data 28 37
Angular JS Route 3 54
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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

16 Experts available now in Live!

Get 1:1 Help Now