日期參數未按照給定日期獲取記錄

CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');

INSERT INTO #ItemMasterFile VALUES
 (1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);


INSERT INTO #Probale VALUES 
  (1,1,1,001,100,'01-06-2019',null)
, (2,3,1,001,200,'02-06-2019',null)
, (3,11,1,002,200,'03-06-2019',null)
, (4,10,1,002,200,'08-06-2019',null)
, (4,1,1,003,200,'08-06-2019',null)
, (4,3,1,003,200,'08-06-2019',null);

INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
, (002,'B_01','01-05-2019','Open')
, (003,'C_01','01-05-2019','Open');

INSERT INTO #OrderDetail VALUES
 (1,001,1,5)
, (2,001,2,3)
, (3,001,3,2)
, (4,002,10,4)
, (5,002,11,3)
, (6,002,3,2)
, (7,003,1,4)
, (8,003,2,3)
, (9,003,3,2);

declare @fromdate date='2019-01-06'
declare @todate date='2019-01-06'
declare @columns varchar(max)
declare @convert varchar(max)
select   @columns = stuff (( select distinct'],[' +  Cast(S.Order_Ref_No AS Varchar(10))
                   from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
and P.DelID is null and S.status ='Open'
                 
                   for xml path('')), 1, 2, '') + ']'


set @convert =
('select * from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.DelID is null 

) smallbale
   pivot(sum(prdqty) for Order_Ref_No
   in ('+@columns+')) as pivottable')


execute (@convert)


drop table #SalesOrder
drop table #OrderDetail
drop table #Sections
drop table #ItemMasterFile
drop table #Probale

以這種格式插入日期“2019-05-07”

但是您沒有以那種（也是不安全的）格式插入日期。從問題：

INSERT INTO #Probale VALUES 
  (1,1,1,001,100,'01-06-2019',null)

你不應該使用yyyy-mm-dd（你在腳本的其他部分這樣做），你絕對不應該使用mm-dd-yyyyor dd-mm-yyyy。我們有標準正是因為我們需要避免這種歧義（如果我不知道您是指 6 月 1 日還是 1 月 6 日，SQL Server 怎麼能？）。這不是您的問題的根源，但它沒有幫助。您需要解決此問題並始終使用yyyymmmdd.

進入您的查詢邏輯。你的支點最終是：

select * from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P 
inner join #salesorder S on S.OrderNo=P.orderno where  P.DelID is null 

) smallbale
   pivot(sum(prdqty) for Order_Ref_No
   in ([A_01])) as pivottable

如果您仍想按日期過濾，這是不正確的，因為它獲取的是該訂單參考號的所有數量的總和，而不僅僅是在指定日期發生的數量（如果您查看#Probale訂單 1 中的行，有兩行，從 6 月 2 日開始）。

您將必須在整個腳本中正確且明確地聲明日期（參見this和this），並切換到 usingsp_executesql而不是，EXEC()這樣您就可以安全地將日期參數傳遞到動態 SQL 中。這並不能修復您的整個腳本，但應該足以幫助您解決問題。

declare @fromdate date = '20190601', -- June 1st, no question.
       @todate   date = '20190601', -- fix other dates too!
       @columns  nvarchar(max),     -- these should be Unicode
       @convert  nvarchar(max);     -- this has to be Unicode 

select @columns = stuff (( select distinct '],[' + Cast(S.Order_Ref_No AS Varchar(10))
    from #Probale P inner join #salesorder S on S.OrderNo = P.orderno 
    where  P.Entrydate >= @fromdate 
      and  P.Entrydate < DATEADD(DAY, 1, @todate)
    and P.DelID is null and S.status = 'Open'
    for xml path('')), 1, 2, '') + ']';

SET @convert = N'select * from (select S.Order_Ref_No,P.codeitem,P.prdqty 
  from #Probale P 
  inner join #salesorder S on S.OrderNo = P.orderno 
  where  P.DelID is null -- missing status = 'Open' too, maybe
  AND P.Entrydate >= @datefrom 
  AND P.Entrydate < DATEADD(DAY, 1, @dateto)
) smallbale
pivot(sum(prdqty) for Order_Ref_No in ('+@columns+')) as pivottable';

EXEC sys.sp_executesql @convert, N'@datefrom date, @dateto date', @datefrom, @dateto;

您可能還會發現這篇文章BETWEEN很有用 - 這是用於日期範圍查詢的不好形式，即使您date今天只使用數據類型，因為它設置了錯誤的先例/範例並且不能保護您免受未來數據類型的影響變化：

• BETWEEN 和魔鬼有什麼共同點？

引用自：https://dba.stackexchange.com/questions/273682