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

x
?
Solved

Check Whether A Date Lies Between Two Dates Using SQL

Posted on 2010-08-16
5
Medium Priority
?
808 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
[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
5 Comments
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 120 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 120 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 120 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 140 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 Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

705 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