I need to compare 2 dates and return the number of days in between with 2 decimal places.
我需要比较2个日期并返回两个小数位之间的天数。
For example:
例如：
when comparing
比较时
SubmittedDate = 2012-02-29 07:02:55.000
FirstCall = 2012-02-29 12:12:19.000
That is a 5 hour difference. So I would return 0.2 days
这是一个5小时的差异。所以我会回来0.2天
I have tried:
我努力了：
CAST(DATEDIFF(Hour, SubmittedDate, FirstCall)/30.0 AS DECIMAL(5,2)) As HoursBeforeFirstCall
CAST(DATEDIFF(Day, SubmittedDate, FirstCall) AS DECIMAL(5,2)) As HoursBeforeFirstCall
None seem to work.
似乎没有工作。
4 个解决方案
#1
33
Take the DateDiff in seconds instead, and then divide by 86400.0. The decimal point is required.
以秒为单位取DateDiff，然后除以86400.0。小数点是必需的。
#2
4
When you represent a date as a number, each day is represented by 1.0 "units" already. To get the timespan of two dates as a decimal, you can just subtract them.
当您将日期表示为数字时，每天由1.0“单位”表示。要将两个日期的时间跨度作为小数，您可以减去它们。
SELECT CAST((@FirstCall - @SubmittedDate) AS NUMERIC(10, 2))
#3
1
How about this.
这个怎么样。
select convert(decimal(12,2),convert(decimal(12,2),datediff(second,'2012-02-29 07:02:55.000','2012-02-29 12:12:19.000'))/60/60/24)
#4
0
Here is what I've done:
这就是我所做的：
ROUND(SUM(DATEDIFF(ss,StartDateTime,EndDateTime) / 60.0 / 60.0), 2)
#1
33
Take the DateDiff in seconds instead, and then divide by 86400.0. The decimal point is required.
以秒为单位取DateDiff，然后除以86400.0。小数点是必需的。
#2
4
When you represent a date as a number, each day is represented by 1.0 "units" already. To get the timespan of two dates as a decimal, you can just subtract them.
当您将日期表示为数字时，每天由1.0“单位”表示。要将两个日期的时间跨度作为小数，您可以减去它们。
SELECT CAST((@FirstCall - @SubmittedDate) AS NUMERIC(10, 2))
#3
1
How about this.
这个怎么样。
select convert(decimal(12,2),convert(decimal(12,2),datediff(second,'2012-02-29 07:02:55.000','2012-02-29 12:12:19.000'))/60/60/24)
#4
0
Here is what I've done:
这就是我所做的：
ROUND(SUM(DATEDIFF(ss,StartDateTime,EndDateTime) / 60.0 / 60.0), 2)