我有一个存储过程,它查询我的数据库以提取"昨天"日期的"打开"报告。 该程序将在Windows调度程序上运行,始终查询数据库以获取昨天的结果。 我想要昨天的所有结果,除了上午 5:30 - 6:26。 我不在乎看到这些结果...一切正常,除了不显示指定的时间段...
我的存储过程是:
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
并不总是像您期望的那样工作。
试试
AND NOT (Register_Till_Count_Datetime between @dtExcludeFrom and @dtExcludeThru )