转换在存储过程中的字符串转换日期和/或时间时失败

Question

我有2个类似的存储过程，但是由于某种原因，其中一个在从字符串转换为日期格式时起作用，但另一个则没有：

这是不起作用的：

exec Search_PR 'US00000001','PR00000001', '01/01/0001', '01/01/0001', 'WH00000001', 'ST00000001','Requested'

代码：

Create Procedure Search_PR 
    (@usersID CHAR(10),
     @prNo VARCHAR(20), 
     @prDateFrom Date, 
     @prDateTo Date, 
     @warehouseID CHAR(10), 
     @storageID CHAR(10), 
     @status VARCHAR(20)
   )
As
Begin
    --some other codes
    Select * 
    From PurchaseReturn pr, storage s, warehouse w, Users u 
    Where pr.storageID = s.storageID 
      and s.warehouseID = w.warehouseID 
      and w.usersID = u.usersID 
      and u.usersID = @usersID 
      and ((pr.prNo like '%' + @prNo + '%') or @prNo Is Null) 
      and (pr.prDate >= @prDateFrom Or @prDateFrom Is Null) 
      and (pr.prDate <= @prDateTo Or @prDateTo Is Null) 
      and (w.warehouseID = @warehouseID or @warehouseID Is Null) 
      and (pr.storageID = @storageID or @storageID Is Null) 
      and (pr.status = @status Or @status Is Null)
End

我从上面的过程中获取此错误：

转换日期和/或从字符串转换时间时失败。

这是行之有效的过程，该过程不会引发相同的错误：

exec Search_PO 'US00000001','PO00000001','01/01/2017','01/01/0001','--Select--'
exec Search_PO 'US00000001','1','01/01/0001','01/01/0001','--Select--'

代码：

Create Procedure Search_PO
    (@usersID CHAR(10), 
     @poNo VARCHAR(20), 
     @poDate Date, 
     @poDeliveryDate Date, 
     @status VARCHAR(20)
    )
As
Begin
    --some other codes
    Select * 
    From PurchaseOrder p, Users u, Warehouse w 
    Where p.warehouseID = w.warehouseID 
      and w.usersID = u.usersID 
      and u.usersID = @usersID 
      and ((p.poNo like '%' + @poNo + '%') or @poNo Is Null) 
      and (p.poDate>=@poDate Or @poDate Is Null) 
      and (p.poDeliveryDate >= @poDeliveryDate Or @poDeliveryDate Is Null) 
      and (p.status = @status Or @status Is Null)
End

感谢提前的帮助

Answer 1

您的加入语法是古老而可怕的。

不好的习惯：使用老式加入 - 亚伦·伯特兰(Aaron Bertrand(

另外，指定字符串date时，最好将'00010101'用于'0001-01-01'。

• 不好的习惯：误解日期/范围查询-Aaron Bertrand

• #backtobasics：负责任地约会-Aaron Bertrand

尝试将pr.prDate明确转换为date。

create procedure Search_pr(
    @usersid char(10) 
  , @prNo varchar(20)
  , @prDateFrom date
  , @prDateTo date
  , @warehouseid char(10)
  , @storageid char(10)
  , @status varchar(20)
  ) As
begin
set nocount on;
--some other codes
select *
from PurchaseReturn pr
 , storage s
 , warehouse w
 , Users u
where pr.storageid = s.storageid
  and s.warehouseid = w.warehouseid
  and w.usersid = u.usersid
  and u.usersid = @usersid
  and ((pr.prNo like '%' + @prNo + '%') or @prNo is null)
  and (convert(date,pr.prDate) >= @prDateFrom or @prDateFrom is null)
  and (convert(date,pr.prDate) <= @prDateTo or @prDateTo is null)
  and (w.warehouseid = @warehouseid or @warehouseid is null)
  and (pr.storageid = @storageid or @storageid is null)
  and (pr.status = @status or @status is null)
end;