在 DATETIME 中使用 BETWEEN 进行 Sql Server 查询的问题
我有一个存储过程,它循环遍历会计年度中的月份,并对每个月的项目进行计数。我知道事实上有 176 个项目,但当我运行它时,它返回的总计数为 182。我尝试从 @EndDate 中删除一秒,但后来我的总计数为 165。所以我要么对项目进行两次计数,要么不算全部。谁能帮我解决我在这里做错的事情吗?下面是我正在做的事情的精简版本:
DECLARE @Date DATETIME
DECLARE @EndDate DATETIME
SELECT @Date = CAST((@Year - 1) as VARCHAR) + '-07-01'
SELECT @EndDate = DATEADD(Month, 1, @Date)
DECLARE @Count INT
SELECT @Count = 0
WHILE @Count < 12
BEGIN
SELECT
COUNT(yai.ID)
FROM
table_1 yai
INNER JOIN table_2 yat ON yai.ID = yat.ID
WHERE
(yat.Date_Received BETWEEN CONVERT(VARCHAR, @Date, 101) AND CONVERT(VARCHAR, @EndDate, 101)) AND
yai.Pro_Type = @Value AND yat.Type = 'PC'
SELECT @Count = @Count + 1
SELECT @Date = DATEADD(MONTH, 1, @Date)
SELECT @EndDate = DATEADD(MONTH, 1, @EndDate)
END
I have a Stored Procedure that loops through the months in the fiscal year and does a count for the items in each month. I know for a fact there are 176 items, but when I run this it returns a total count of 182. I tried removing one second from @EndDate, but then my total count was 165. So I'm either counting items twice, or not counting all of them. Can anyone help with what I'm doing wrong here? Below is a stripped down version of what I'm doing:
DECLARE @Date DATETIME
DECLARE @EndDate DATETIME
SELECT @Date = CAST((@Year - 1) as VARCHAR) + '-07-01'
SELECT @EndDate = DATEADD(Month, 1, @Date)
DECLARE @Count INT
SELECT @Count = 0
WHILE @Count < 12
BEGIN
SELECT
COUNT(yai.ID)
FROM
table_1 yai
INNER JOIN table_2 yat ON yai.ID = yat.ID
WHERE
(yat.Date_Received BETWEEN CONVERT(VARCHAR, @Date, 101) AND CONVERT(VARCHAR, @EndDate, 101)) AND
yai.Pro_Type = @Value AND yat.Type = 'PC'
SELECT @Count = @Count + 1
SELECT @Date = DATEADD(MONTH, 1, @Date)
SELECT @EndDate = DATEADD(MONTH, 1, @EndDate)
END
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(7)
两者之间是包含在内的,因此您应该减去 1 秒(甚至一天)。我的猜测是有些 yais 没有相应的 yat。
编辑:你的代码是假的。除了与格式 101 相等之外,您无法进行其他比较。
The between is inclusive, so your 1-second subtract should be there (or even a day). My guess is that some yais have no corresponding yat.
Edit: Your code is bogus. You can't do comparisons other than equality with the format 101.
从我的头顶。
From the top of my head.
我曾经说过一些有趣的话,比如我知道有 50 个项目,而 sql 返回了 60 个……
我发现我总是错的! :)
尝试从日期和时间字段中删除时间:
DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
I used to say funny things like I know for a fact there is 50 items and sql returned 60...
I found out I was always wrong! :)
Try stripping the time from the date and time fields:
DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
你不能这样做吗?以下查询将为您提供按月分组的计数。如果将其放入视图中,则可以从视图中进行选择并使用 where 子句按您感兴趣的年份和月份进行筛选。
Couldn't you do this instead? The following query will give you counts grouped by month. If you put this in a view, you can then select from the view and use a where clause to filter by the year and month you are interested in.
这是 SQL 代码中不必要的循环的典型案例,但如果您想用循环解决此问题,请将循环中的选择从:更改
为:,
然后查看输出并检查返回的行
this is a classic case of an unnecessary loop is SQL code, but if you want to solve this with a loop, change your select in the loop from:
to:
and then look at the output and check the rows returned
为什么不将日历月份转换为绝对整数引用,如下所示:
Why not convert the calendar months into absolute integer references like this:
我发现这个问题是因为我在使用 Between 的查询中使用字符串日期时遇到了麻烦,并且我正在使用元数据,我的同事帮助了我,所以我希望这也对您有帮助:
I found this question because I had troubles with using string date in queries using Between and I was using metadata, my co-worker helped me, so I hope this helps you too: