Date Exists between Range of Dates not working

Question

0.00/5 (No votes)

See more:

SQL

ALTER PROCEDURE [dbo].[spInsert]
(@PlanName Varchar(50)=null
,@StartDate Datetime
,@EndDate Datetime
,@ModifiedBy Varchar(100)=null
,@ReturnValue Int Out)
As
BEGIN
IF NOT EXISTS(SELECT PlanName FROM dbo.tblPlan WHERE PlanName=@PlanName)
BEGIN
IF((SELECT COUNT(*) FROM tblPlan WHERE StartDate <= @StartDate  AND EndDate <=
    @EndDate)<0)
 BEGIN
 INSERT INTO dbo.tblPlan VALUES(3,@PlanName,@StartDate,@EndDate,@ModifiedBy,GETDATE(),
       (SELECT DATEDIFF(DD,@StartDate,@EndDate)))
 SET @ReturnValue=1;
 END
ELSE
SET @ReturnValue=-2;
END
ELSE
SET @ReturnValue=-1;
END

I am trying to achieve the below thing.I want to check user supplied startDate and Enddate is in between the existing table startdate and enddate. if any of the date of user supplied date range is in between the tables start date and end date,it should retrun -2,if the reocrd doesnt exists it should insert the details..It doesnt insert even the start and End date not with in the range.

I Could not achieve this logic.where i went wrong ..please suggest me any solution to this.

Posted 6-Oct-13 19:04pm

keerth516

Add a Solution

Comments

ArunRajendra 7-Oct-13 1:09am

I guess it should be StartDate >= @StartDate instead of StartDate <= @StartDate

keerth516 7-Oct-13 1:15am

IF((SELECT COUNT(*) FROM tblPlan WHERE StartDate>=@StartDate AND EndDate <=@EndDate)<0)

keerth516 7-Oct-13 1:16am

i tried this way also...but it will insert if it in the range but it will not insert either if it not with in the range

3 solutions

Solution 1

Try the below queries -

SQL

--For SQL 2008 or Higher
SELECT COUNT(*) FROM tblPlan WHERE CONVERT(DATE,StartDate,103) >= CONVERT(DATE,@StartDate,103)  AND CONVERT(DATE,EndDate,103) <= CONVERT(DATE,@EndDate,103)

SQL

--Pre SQL 2008
SELECT COUNT(*) FROM tblPlan WHERE CONVERT(VARCHAR,StartDate,103) >= CONVERT(VARCHAR,@StartDate,103)  AND CONVERT(VARCHAR,EndDate,103) <= CONVERT(VARCHAR,@EndDate,103)

Posted 6-Oct-13 19:41pm

Madhu Nair

Solution 2

Hey there,

First of all,

Try =0 instead of <0 here:

SQL

IF((SELECT COUNT(*) FROM tblPlan WHERE StartDate >= @StartDate  AND EndDate <=
    @EndDate)= 0)

and if your Dates DataType is DateTime you might wanna Convert both sides of Dates comparison to specific dates. check this: http://www.w3schools.com/sql/func_convert.asp[^]

Hope it helps, let me know.

Azee...

Posted 6-Oct-13 19:46pm

Azee

Comments

keerth516 7-Oct-13 2:03am

it keeps inserting data even Curret start and end date is with in the already existed date range..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

keerth516 · Accepted Answer · 2013-10-06T23:32:00

ALTER PROCEDURE [dbo].[spInsertPlan]
(@PlanName Varchar(50)=null
,@StartDate Datetime
,@EndDate Datetime
,@ModifiedBy Varchar(100)=null
,@ReturnValue Int Out)
As
DECLARE @PlanCode int;
SET @PlanCode=(SELECT MAX(PlanCode) FROM dbo.tblPlan);
IF(@PlanCode IS NULL)
SET @PlanCode=1;
ELSE
SET @PlanCode=@PlanCode+1;
BEGIN

IF NOT EXISTS(SELECT PlanName FROM dbo.tblPlan WHERE PlanName=@PlanName)
BEGIN

IF (SELECT COUNT(*) FROM tblPlan WHERE StartDate >@EndDate
OR @StartDate Between (StartDate) AND (EndDate)
OR @EndDate Between (StartDate) AND (EndDate)
OR EndDate Between (@StartDate) AND (@EndDate))>0
BEGIN
SET @ReturnValue=-2;
END
ELSE
BEGIN
INSERT INTO dbo.tblPlan VALUES(@PlanCode,@PlanName,@StartDate,@EndDate,@ModifiedBy,GETDATE(), (SELECT DATEDIFF(DD,@StartDate,@EndDate)))
SET @ReturnValue=1;
END
END
ELSE
SET @ReturnValue=-1;
END