SQL 我想要小时，分钟，两个日期时间的第二个差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5550677/
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
I want Hours,Min, second difference from two datetime
提问by Kishan Gajjar
I am developing Time management system for employees.
我正在为员工开发时间管理系统。
I want the duration how much duration employee come late , or he went early.
我想要员工迟到或早到的持续时间。
i have following structure.
我有以下结构。
**Attendace**
AutoId --uniqueidentifier
EMployeeId --uniqueidentifier
Date --datetime
InTime -- varchar(50)
OutTime -- varchar(50)
ActualInTime--datetime
ActualOutTime--datetime
I want Late Coming Report ( i.e. who came late in morning after ActualInTime and how much duration in hh:mm:ss ) and also want early going(i.e who went early in the evening before ActualOutTime in duration in format hh:mm:ss )
我想要迟到报告（即谁在 ActualInTime 之后早上迟到以及 hh:mm:ss 中的持续时间是多少）并且还想要早点（即谁在 ActualOutTime 之前的晚上早点到达，格式为 hh:mm:ss ）
So can you please help me..???
所以你能帮我吗..？？？
采纳答案by Annu
this code might help you...
此代码可能会帮助您...
DECLARE @First datetime
DECLARE @Second datetime
SET @First = '04/02/2008 05:23:22'
SET @Second = getdate()
SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,
DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,
DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds
回答by lynx_74
You can do it in a very simple way:
你可以用一种非常简单的方式来做到这一点：
declare @date1 datetime, @date2 datetime
set @date1=DATEADD(s,-638,getdate())
set @date2=GETDATE()
select convert(char(8),dateadd(s,datediff(s,@date1,@date2),'1900-1-1'),8)
... the result is 00:10:38(638s = 600s + 38s = 10 minutes and 38 seconds)
... 结果是00:10:38（638s = 600s + 38s = 10 分 38 秒）
Another example:
另一个例子：
select distinct convert(char(8),dateadd(s,datediff(s, CRDATE , GETDATE() ),'1900-1-1'),8) from sysobjects order by 1
It will works until the difference of 86399 seconds (23:59:59):
它将一直工作到 86399 秒 (23:59:59) 的差异：
select convert(char(8),dateadd(s,datediff(s
, DATEADD(s,-86399,GETDATE())
, GETDATE()
),'1900-1-1'),8)
... after that it will return to zero:
...之后它将返回零：
select convert(char(8),dateadd(s,datediff(s
, DATEADD(s,-86400,GETDATE())
, GETDATE()
),'1900-1-1'),8)
回答by Mike Gledhill
I came across an easier way of solving this issue.
我遇到了一种更简单的方法来解决这个问题。
First, a quick example of turning a "number of seconds" into the "hh:mm:ss" format.
首先，将“秒数”转换为“hh:mm:ss”格式的快速示例。
DECLARE @NumberOfSeconds int
SET @NumberOfSeconds = 3843 -- 1 hour, 4 minutes, 3 seconds
SELECT @NumberOfSeconds AS 'seconds',
CONVERT(varchar, DATEADD(second, @NumberOfSeconds, 0), 108) AS 'hh:mm:ss'
This will give us this output:
这将为我们提供以下输出：
And we can easily take this a step further, calculate the number of seconds between two datetimes, and display it in hh:mm:ssformat:
我们可以轻松地更进一步，计算两个日期时间之间的秒数，并以hh:mm:ss格式显示：
DECLARE
@NumberOfSeconds int,
@StartTime datetime = '2017-09-14 14:16:11',
@EndTime datetime = '2017-09-14 14:23:13'
SET @NumberOfSeconds = DATEDIFF(second, @StartTime, @EndTime)
SELECT @NumberOfSeconds AS 'seconds',
CONVERT(varchar, DATEADD(second, @NumberOfSeconds, 0), 108) AS 'hh:mm:ss'
Which gives us this output:
这给了我们这个输出：
Simple, hey ?
简单，嘿？
(And yes, you can simplify it further by putting the DATEDIFFdirectly into the DATEADDfunction.)
（是的，您可以通过将DATEDIFF直接放入DATEADD函数来进一步简化它。）
回答by RichardTheKiwi
Because they are the same day (you don't have to worry about number of hours >24), you can just use a combination of DATEDIFF(second,time1,time2) and DATEADD(second,0,) to get a datetime value.
因为它们是同一天（您不必担心小时数 >24），您可以只使用 DATEDIFF(second,time1,time2) 和 DATEADD(second,0,) 的组合来获取日期时间值.
To format to hh:nn:ss, use convert(char(8),answer,8)but this is something better done by the reporting front end against the datetime result.
要将格式设置为 hh:nn:ss，请使用，convert(char(8),answer,8)但这是由报告前端针对日期时间结果做的更好的事情。
-- Late report
select *, dateadd(s,0,datediff(s,intime,actualintime)) late_by
from attendance
where intime < actualintime
回答by Marvin Zumbado
well, yes, you need to use DATEDIFF, and yes, all that posted above works, but, if you want to show 07:07:07 instead of 7:7:7, you have to do something like this:
好吧，是的，您需要使用 DATEDIFF，是的，上面发布的所有内容都有效，但是，如果您想显示 07:07:07 而不是 7:7:7，则必须执行以下操作：
Declare @starttime datetime, @endtime datetime, @seconds int, @minutes int, @hours int
Set @starttime ='2013-10-01 05:05:17'
Set @endtime = '2013-10-01 23:10:18'
set @hours = DateDiff(hour, @starttime, @endtime)
set @minutes = DateDiff(minute, @starttime, @endtime);
set @seconds = DateDiff(second, @starttime, @endtime);
select case when DateDiff(minute, @starttime, @endtime) > 60
then CASE WHEN @hours >= 10 THEN cast(@hours as varchar(3))
ELSE '0' + cast(@hours as varchar(3)) END +':' +
CASE WHEN @minutes - (@hours * 60) >= 10 THEN
cast((@minutes - (@hours * 60)) as varchar(3))
ELSE '0' +cast((@minutes - (@hours * 60)) as varchar(3)) END
+ CASE WHEN (@seconds - (@minutes *60)) >= 10 THEN
+':' + cast(@seconds - (@minutes *60) as varchar(10))
ELSE ':0' + cast(@seconds - (@minutes *60) as varchar(10)) END
ELSE '0' + cast(@minutes as varchar(3)) +':' + cast(@seconds as varchar(10))
end
It may not look very nice, but it gave me what i wanted.
它可能看起来不太好，但它给了我想要的东西。
回答by Kyle Cowden
How about using CAST.
使用 CAST 怎么样。
,CAST (Table1.DateTimeLatest-Table1.DateTimeFirst as time) as [Elapsed Time]
The raw result from SSMS from an apparatus table: SQL Return shows out to nanoseconds in this Data.
来自仪器表的 SSMS 的原始结果： SQL Return 在此数据中显示为纳秒级。
For the report, as pasted in formatted Excel sheet: Formatted result column as hh:mm:ss.
对于报告，粘贴在格式化的 Excel 工作表中： 格式化结果列为 hh:mm:ss。
回答by ShaneOss
Create a stored procedure to do the work and then just call the procedure passing your start and end dates.
创建一个存储过程来完成这项工作，然后调用传递开始和结束日期的过程。
CREATE PROCEDURE [dbo].[GetOperationDuration]
@DurationStart DATETIME, @DurationEnd DATETIME,
@Duration VARCHAR(100) OUTPUT
AS
BEGIN
DECLARE @years INT, @months INT, @days INT,
@hours INT, @minutes INT, @seconds INT, @milliseconds INT;
-- DOES NOT ACCOUNT FOR LEAP YEARS
SELECT @years = DATEDIFF(yy, @DurationStart, @DurationEnd)
IF DATEADD(yy, -@years, @DurationEnd) < @DurationStart
SELECT @years = @years-1
SET @DurationEnd = DATEADD(yy, -@years, @DurationEnd)
SELECT @months = DATEDIFF(mm, @DurationStart, @DurationEnd)
IF DATEADD(mm, -@months, @DurationEnd) < @DurationStart
SELECT @months=@months-1
SET @DurationEnd= DATEADD(mm, -@months, @DurationEnd)
SELECT @days=DATEDIFF(dd, @DurationStart, @DurationEnd)
IF DATEADD(dd, -@days, @DurationEnd) < @DurationStart
SELECT @days=@days-1
SET @DurationEnd= DATEADD(dd, -@days, @DurationEnd)
SELECT @hours=DATEDIFF(hh, @DurationStart, @DurationEnd)
IF DATEADD(hh, -@hours, @DurationEnd) < @DurationStart
SELECT @hours=@hours-1
SET @DurationEnd= DATEADD(hh, -@hours, @DurationEnd)
SELECT @minutes=DATEDIFF(mi, @DurationStart, @DurationEnd)
IF DATEADD(mi, -@minutes, @DurationEnd) < @DurationStart
SELECT @minutes=@minutes-1
SET @DurationEnd= DATEADD(mi, -@minutes, @DurationEnd)
SELECT @seconds=DATEDIFF(s, @DurationStart, @DurationEnd)
IF DATEADD(s, -@seconds, @DurationEnd) < @DurationStart
SELECT @seconds=@seconds-1
SET @DurationEnd= DATEADD(s, -@seconds, @DurationEnd)
SELECT @milliseconds=DATEDIFF(ms, @DurationStart, @DurationEnd)
SELECT @Duration= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
+ ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')
+ ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
+ ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
+ ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
+ ISNULL(' ' + CAST(@seconds AS VARCHAR(10))
-- UNCOMMENT THEFOLLOWING IF YOU WANT MILLISECONDS INCLUDED
--+ CASE
--WHEN @milliseconds > 0
--THEN '.' + CAST(@milliseconds AS VARCHAR(10))
--ELSE ''
--END
+ ' seconds','')
SELECT @Duration
END
GO
Then just call using: DECLARE @return_value int, @Duration varchar(100)
然后调用使用： DECLARE @return_value int, @Duration varchar(100)
EXEC @return_value = [dbo].[GetOperationDuration] @DurationStart, @DurationEnd, @Duration = @Duration OUTPUT
EXEC @return_value = [dbo].[GetOperationDuration] @DurationStart, @DurationEnd, @Duration = @Duration OUTPUT
SELECT @Duration as N'@Duration'
SELECT @Duration 作为 N'@Duration'
回答by Strider489
SELECT id, pickupdateandtime, GETDATE() AS CurrentTime,
((DATEDIFF(day,GETDATE(),pickupdateandtime)) - 1) AS Days ,
convert(char(8),dateadd(s,datediff(s,GETDATE(),pickupdateandtime),'1900-1-
1'),8) AS 'Hours & Mins' FROM orders
Here's what worked for me. Thank you @lynx_74.
这对我有用。谢谢@lynx_74。
