SQL 如何在sql中减去两个日期列和两个时间列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23521088/
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
How do I subtract two date columns and two time columns in sql
提问by user3537618
I have 4 columns that are
我有 4 列是
Startdate,
enddate,
starttime,
endtime.
I need to get subtractions from the enddate and endtime- startdate and starttime. I will need that answer from the four columns in sql.
我需要从enddate and endtime- 中减去startdate and starttime。我将需要 sql 中四列的答案。
so far I have this I found but dont think this will work right.
到目前为止，我找到了这个，但不认为这会正常工作。
SELECT DATEDIFF (day, enddate, startdate) as NumberOfDays
DATEDIFF(hour,endtime,starttime) AS NumberOfHours
DATEDIFF(minute,endtime,starttime) AS NumberOfMinutes
from table;
Thanks for your help
谢谢你的帮助
回答by Jeremy Hutchinson
Assuming you have data like this, you can add the StartDate to the StartTime to get the StartDateTime, same for the EndDateTime
假设您有这样的数据，您可以将 StartDate 添加到 StartTime 以获取 StartDateTime，与 EndDateTime 相同
StartDate StartTime EndDate EndTime
----------------------- ----------------------- ----------------------- -----------------------
2014-05-01 00:00:00.000 1900-01-01 10:53:28.290 2014-05-07 00:00:00.000 1900-01-01 11:55:28.290
Once you've done that you can get the Days, Hours and Minutes like this:
完成后，您可以获得如下所示的天数、小时数和分钟数：
select
DATEDIFF(minute, StartDate + StartTime, EndDate + EndTime) / (24*60) 'Days',
(DATEDIFF(minute, StartDate + StartTime, EndDate + EndTime) / 60) % 24 'Hours',
DATEDIFF(minute, StartDate + StartTime, EndDate + EndTime) % 60 'Minutess'
from YourTable
We have work in minutes the whole time in order to prevent problems with partial days crossing midnight and partial hours crossing an hour mark.
我们始终在几分钟内完成工作，以防止部分时间跨越午夜和部分时间跨越小时标记的问题。
回答by dev8675309
EDIT - Now that I realize that the question is for SQL Server 2000, this proposed answer may not work.
编辑 - 现在我意识到问题是针对 SQL Server 2000 的，这个建议的答案可能不起作用。
The SQL Server 2000 documentation can be found at https://www.microsoft.com/en-us/download/details.aspx?id=18819. Once installed, look for tsqlref.chm in your installed path, and in that help file you can find information specific to DATEDIFF.
SQL Server 2000 文档可以在https://www.microsoft.com/en-us/download/details.aspx?id=18819找到。安装后，在安装路径中查找 tsqlref.chm，在该帮助文件中，您可以找到特定于 DATEDIFF 的信息。
Based on the wording of the original question, I'm assuming that the start/end time columns are of type TIME, meaning there is no date portion. With that in mind, the following would answer your question.
根据原始问题的措辞，我假设开始/结束时间列的类型为 TIME，这意味着没有日期部分。考虑到这一点，以下内容将回答您的问题。
However, note that depending on your data, you will lose precision in regards to the seconds and milliseconds.
但是，请注意，根据您的数据，您将失去关于秒和毫秒的精度。
More about DATEDIFF: https://msdn.microsoft.com/en-us/library/ms189794.aspx
有关 DATEDIFF 的更多信息：https: //msdn.microsoft.com/en-us/library/ms189794.aspx
DECLARE @mytable AS TABLE
(
startdate DATETIME,
enddate DATETIME,
starttime TIME,
endtime TIME
)
INSERT INTO @mytable (startdate, enddate, starttime, endtime)
VALUES (GETDATE() - 376, GETDATE(), '00:00:00', '23:59')
SELECT *
FROM @mytable
SELECT DATEDIFF(HOUR, startdate, enddate) AS [NumHours],
DATEDIFF(MINUTE, starttime, endtime) AS [NumMinutes]
FROM @mytable
This would yield output similar to:
这将产生类似于以下内容的输出：
