sql - 计算两个日期之间记录的存储过程

Question

我正在尝试计算 SQL Server 2008 中两个日期之间状态为 0 的记录有我的程序，但它不是正确的错误。

Msg 156, Level 15, State 1, Procedure sp_SerchPickupHispanicBetweenDates, Line 6
Incorrect syntax near the keyword 'between'.

程序看起来像

CREATE procedure sp_SerchPickupHispanicBetweenDates
@date1 date,
@date2 date
as 
select COUNT ( Pickuphispanic ) from  pickup
where  Pickuphispanic = 1 and pickupdate like between @date1+ '%' and @date1 +'%'

尝试在互联网上搜索解决方案但没有成功。任何想法如何纠正它？我还有 select 语句，效果很好

select COUNT ( Pickuphispanic ) from  pickup
where  Pickuphispanic = 1 and pickup.pickupdate between '2006-07-01' and '2010-12-31'

Answer 1

@date1 和 @date2 参数的数据类型为 DATE，因此您不能附加 %到那些值(当使用 LIKE 运算符时，% 可以仅用于基于字符串的数据类型；另外:您不能 将 LIKE 应用于使用 DATE 值的搜索。

改用这个:

CREATE procedure procSerchPickupHispanicBetweenDates
   @date1 date,
   @date2 date
AS
   SELECT COUNT (Pickuphispanic) 
   FROM dbo.pickup
   WHERE Pickuphispanic = 1 
     AND pickupdate BETWEEN @date1 AND @date2

要调用它，请使用以下语法:

EXEC procSerchPickupHispanicBetweenDates '2006-07-01', '2010-12-31'

(没有大括号，没有括号 - 只需指定两个日期)，或者:

EXEC procSerchPickupHispanicBetweenDates @date1 = '2006-07-01', @date2 = '2010-12-31'