排除存储过程中的特定时间范围 [英] Exclude a specific range of time in a stored procedure

问题描述

我有一个存储过程，它查询我的数据库以提取昨天"日期的打开"报告.该程序将在 Windows 调度程序上运行，始终查询数据库以获取昨天的结果.我想要昨天的所有结果，除了 5:30AM - 6:26AM.我不在乎看到这些结果...一切正常，除了不显示指定的时间块...

I have a stored procedure that queries my database to pull a "Open" report for "Yesterdays" date. This program will run on windows scheduler always querying the database for yesterdays results. I want all of the results from yesterday EXCEPT 5:30AM - 6:26AM. I do not care to see these results... Everything works, with the exception of not displaying the specified time block...

我的存储过程是:

ALTER PROCEDURE [dbo].[Open_Close] AS
declare   @dtNow         datetime ,   @dtToday       datetime ,   @dtFrom        datetime ,   @dtThru        datetime ,   @dtExcludeFrom datetime ,   @dtExcludeThru datetime  
set @dtNow         = getdate() 
set @dtToday       = convert(datetime,convert(varchar,@dtNow,112),112) 
set @dtFrom        = dateadd(day,-1,@dtToday) -- start-of-day yesterday 
set @dtThru        = dateadd(ms,-3,@dtToday)  -- end-of-day yesterday (e.g., 2012-06-17 23:59:59.997) 
set @dtExcludeFrom = convert(datetime, convert(char(10),@dtFrom,120) + ' 05:30:00.000' , 120 ) 
set @dtExcludeThru = convert(datetime, convert(char(10),@dtFrom,120) + ' 06:15:00.000' , 120 )  

SELECT Store_Id , DM_Corp_Received_Date
FROM Register_Till_Count_Tb 
WHERE Register_Transaction_Type    =  'SOD'   
AND Register_Till_Count_Datetime     between @dtFrom        
and @dtThru  AND Register_Till_Count_Datetime not between @dtExcludeFrom 
and @dtExcludeThru

推荐答案

尝试改变

Register_Till_Count_Datetime not between @dtExcludeFrom and @dtExcludeThru

到

 (Register_Till_Count_Datetime < @dtExcludeFrom  OR 
  Register_Till_Count_Datetime > @dtExcludeThru)

不要忘记括号.

NOT BETWEEN 并不总是如您所愿.

这篇关于排除存储过程中的特定时间范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！