SQL TSQL DateDiff 返回带有 2 个小数位的天数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9524094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL DateDiff to return number of days with 2 decimal places
提问by Internet Engineer
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.
没有一个似乎工作。
回答by John Pick
Take the DateDiffin seconds instead, and then divide by 86400.0. The decimal point is required.
取DateDiff以秒为单位，然后除以86400.0。需要小数点。
回答by HymanAce
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))
回答by Ispep Aloc
Here is what I've done:
这是我所做的：
ROUND(SUM(DATEDIFF(ss,StartDateTime,EndDateTime) / 60.0 / 60.0), 2)
回答by Thit Lwin Oo
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)
