日期范围之间存在日期不起作用 [英] Date Exists between Range of Dates not working
问题描述
ALTER PROCEDURE [dbo]。[spInsert]
( @ PlanName Varchar ( 50 )= null
, @ StartDate 日期时间
, @ EndDate 日期时间
, @ 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
我正在努力实现以下目标。我想检查用户提供的startDate和Enddate是否在现有表startdate和enddate之间。如果用户提供的日期范围的任何日期在表开始日期和结束日期之间,它应该返回-2,如果reocrd不存在,它应该插入详细信息..它甚至不插入开始和结束日期在这个范围内。
我无法实现这个逻辑。我出错了..请给我任何解决方案。
ALTER PROCEDURE [dbo]。[spInsertPlan]
(@PlanName Varchar(50)= null
,@ StartDate Datetime
,@ EndDate日期时间
,@ 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
如果不是EXISTS(选择PlanName FROM dbo.tblPlan WHERE PlanName = @ PlanName)
BEGIN
IF(SELECT COUNT(*)FROM tblPlan WHERE StartDate> @EndDate
OR @StartDate Between(StartDate)AND(EndDate)
或者@EndDate介于(StartDate)和(EndDate)之间
或者在(@StartDate)和(@EndDate)之间的EndDate> 0
BEGIN
SET @ ReturnValue = -2;
END
ELSE
BEGIN
INSERT INTO dbo.tblPlan VALUES (@ PlanCode,@ PlanName,@起始日期,结束日期@,@ ModifiedBy,GETDATE(),(SELECT DATEDIFF(DD,@起始日期,结束日期@)))
SET @返回值= 1;
结束
结束
ELSE
SET @ ReturnValue = -1;
END
尝试以下查询 -
- 对于SQL 2008或更高版本
SELECT COUNT(*)< span class =code-keyword> FROM tblPlan WHERE CONVERT ( DATE ,StartDate, 103 )> = CONVERT (< span class =code-keyword> DATE , @ StartDate , 103 ) AND CONVERT ( DATE ,EndDate, 103 )< = CONVERT ( DATE , @ EndDate , 103 )
< span class =code-comment> - 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 )
嘿那里,
首先,
尝试= 0
而不是的< 0
这里:
IF (( SELECT COUNT(*) FROM tblPlan WHERE StartDate> = @ StartDate AND EndDate< =
@ EndDate )= 0 )
如果您的日期数据类型是日期时间,您可能希望将日期比较的两边转换为特定日期。检查一下: http://www.w3schools.com/sql/func_convert.asp [ ^ ]
希望它有所帮助,请告诉我。
Azee ......
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.
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
Try the below queries -
--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)
--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)
Hey there,
First of all,
Try=0
insteadof <0
here:
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...
这篇关于日期范围之间存在日期不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!