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:
7
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
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