两天之间的T-SQL计数天数（datediff不太正常）

We have a requirement to bill our customers per day. We bill for an asset's existence in our system on that day. So, I started with datediff...

我们要求每天向客户收费。我们当天在我们的系统中为资产的存在收费。所以,我从约会开始......

select datediff(dd  ,'2015-04-24 12:59:32.050'  ,'2015-05-01 00:59:59.000');

Returns this:

But I need to count the following dates: 4/24,4/25,4/26,4/27,4/28,4/29, 4/30, 5/1, which are 8 days. So datediff isn't quite working right. I tried these variations below

但我需要计算以下日期:4 / 24,4 / 25,4 / 26,4 / 27,4 / 28,4 / 29,4 / 30,5 / 1,这是8天。因此,约会是不太正常的。我在下面试过这些变化

--too simple, returns 7, i need it to return 8
select datediff(dd  ,'2015-04-24 12:59:32.050', '2015-05-01 23:59:59.000');

--looking better, this returns the 8 i need
select ceiling(datediff(hh,'2015-04-24 12:59:32.050', '2015-05-01 23:59:59.000')/24.0);

-- returns 7, even though the answer still needs to be 8. (changed enddate)
select ceiling(datediff(hh,'2015-04-24 12:59:32.050', '2015-05-01 00:59:59.000')/24.0);

So, my question... How, in SQL, would I derive the date count like i described, since I believe datediff counts the number of day boundaries crossed.... My current best approach is loop through each day in a cursor and count. Ick.

所以,我的问题......在SQL中,我如何得到像我所描述的日期计数,因为我相信dateiff计算了跨越日界限的数量....我目前最好的方法是在光标中循环每一天计数。伊克。

2 个解决方案

#1

Use CONVERT to get rid of the time part, add 1 to get the desired result:

使用CONVERT去除时间部分,添加1以获得所需的结果:

SELECT DATEDIFF(dd,
                CONVERT(DATE, '2015-04-24 12:59:32.050'),
                CONVERT(DATE, '2015-05-01 00:59:59.000')) + 1;

It turns out the time part does not play any significant role in DATEDIFF when dd is used as the datepart argument. Hence, CONVERT is redundant. This:

事实证明,当dd用作datepart参数时,时间部分在DATEDIFF中不起任何重要作用。因此,CONVERT是多余的。这个:

SELECT DATEDIFF(dd, '2015-04-24 23:59:59.59','2015-05-01 00:00:00.000') + 1

will return 8 as well.

也将返回8。

#2

You could try this which would return 8 days.

您可以尝试这将返回8天。

select datediff(dd ,'2015-04-24 12:59:32.050'  ,CASE DATEDIFF(Second,'2015-05-01 00:00:00.000','2015-05-01 23:59:59.000') WHEN 0 THEN '2015-05-01 23:59:59.000' ELSE DATEADD(dd,+1,'2015-05-01 23:59:59.000') END)

If you want to use variables for your dates then something like this would work.

如果你想为你的日期使用变量,那么像这样的东西就行了。

BEGIN
  DECLARE @StartDate DATETIME
  DECLARE @EndDate DATETIME
  DECLARE @EndDateOnly DATE
  SET @StartDate = '2015-04-24 12:59:32.050'
  SET @EndDate = '2015-05-01 23:59:59.000'
  SET @EndDateOnly = CAST(@EndDate AS DATE)

  SELECT datediff(dd ,@StartDate  ,CASE DATEDIFF(Second,CAST(@EndDateOnly||' 00:00:00.000' AS DATETIME),@EndDate) WHEN 0 THEN @EndDate ELSE DATEADD(dd,+1,@EndDate) END)
END

#1