SQL DateDiff 输出小时和分钟
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21249778/
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
DateDiff to output hours and minutes
提问by Evil rising
my code gives TOTAL HOURS in hours, but i am trying to output something like
我的代码以小时为单位给出了 TOTAL HOURS，但我正在尝试输出类似的内容
TotalHours
8:36
where 8 represents hour part and 36 represents minutes part mean totalHours a person has worked in a single day at office.
其中 8 代表小时部分，36 代表分钟部分，表示一个人在办公室一天工作的总小时数。
with times as (
SELECT t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, t1.RecDate AS [DateVisited]
FROM AtdRecord t1
INNER JOIN
AtdRecord t2
ON t1.EmplID = t2.EmplID
AND t1.RecDate = t2.RecDate
AND t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON t3.EmplID = t1.EmplID
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
)
SELECT EmplID
, EmplName
, InTime
, [TimeOut]
, [DateVisited]
, DATEDIFF(Hour,InTime, [TimeOut]) TotalHours
from times
Order By EmplID, DateVisited
采纳答案by DhruvJoshi
Small change like this can be done
可以做这样的小改动
SELECT EmplID
, EmplName
, InTime
, [TimeOut]
, [DateVisited]
, CASE WHEN minpart=0
THEN CAST(hourpart as nvarchar(200))+':00'
ELSE CAST((hourpart-1) as nvarchar(200))+':'+ CAST(minpart as nvarchar(200))END as 'total time'
FROM
(
SELECT EmplID, EmplName, InTime, [TimeOut], [DateVisited],
DATEDIFF(Hour,InTime, [TimeOut]) as hourpart,
DATEDIFF(minute,InTime, [TimeOut])%60 as minpart
from times) source
回答by MaurGi
Very simply:
很简单：
CONVERT(TIME,Date2 - Date1)
For example:
例如：
Declare @Date2 DATETIME = '2016-01-01 10:01:10.022'
Declare @Date1 DATETIME = '2016-01-01 10:00:00.000'
Select CONVERT(TIME,@Date2 - @Date1) as ElapsedTime
Yelds:
产量：
ElapsedTime
----------------
00:01:10.0233333
(1 row(s) affected)
回答by Vignesh Kumar A
Try this query
试试这个查询
select
*,
Days = datediff(dd,0,DateDif),
Hours = datepart(hour,DateDif),
Minutes = datepart(minute,DateDif),
Seconds = datepart(second,DateDif),
MS = datepart(ms,DateDif)
from
(select
DateDif = EndDate-StartDate,
aa.*
from
( -- Test Data
Select
StartDate = convert(datetime,'20090213 02:44:37.923'),
EndDate = convert(datetime,'20090715 13:24:45.837')) aa
) a
Output
输出
DateDif StartDate EndDate Days Hours Minutes Seconds MS
----------------------- ----------------------- ----------------------- ---- ----- ------- ------- ---
1900-06-02 10:40:07.913 2009-02-13 02:44:37.923 2009-07-15 13:24:45.837 152 10 40 7 913
(1 row(s) affected)
回答by Damien_The_Unbeliever
I would make your final select as:
我会让你的最终选择为：
SELECT EmplID
, EmplName
, InTime
, [TimeOut]
, [DateVisited]
, CONVERT(varchar(3),DATEDIFF(minute,InTime, TimeOut)/60) + ':' +
RIGHT('0' + CONVERT(varchar(2),DATEDIFF(minute,InTime,TimeOut)%60),2)
as TotalHours
from times
Order By EmplID, DateVisited
Any solution trying to use DATEDIFF(hour,...is bound to be complicated (if it's correct) because DATEDIFFcounts transitions - DATEDIFF(hour,...09:59',...10:01')will return 1 because of the transition of the hour from 9 to 10. So I'm just using DATEDIFFon minutes.
任何尝试使用的解决方案DATEDIFF(hour,...都一定会很复杂（如果它是正确的），因为DATEDIFF计数转换 -DATEDIFF(hour,...09:59',...10:01')由于小时从 9 到 10 的转换，将返回 1。所以我只使用DATEDIFF分钟。
The above can still be subtly wrong if seconds are involved (it can slightly overcount because its counting minute transitions) so if you need second or millisecond accuracy you need to adjust the DATEDIFFto use those units and then apply suitable division constants (as per the hours one above) to just return hours and minutes.
如果涉及秒，上面的内容仍然可能是微妙的错误（它可能会稍微多算，因为它计算分钟转换）所以如果你需要秒或毫秒的精度，你需要调整DATEDIFF使用这些单位，然后应用合适的除法常数（根据小时上面的一个）只返回小时和分钟。
回答by Rhino
Just change the
只需更改
DATEDIFF(Hour,InTime, [TimeOut]) TotalHours
part to
部分到
CONCAT((DATEDIFF(Minute,InTime,[TimeOut])/60),':',
(DATEDIFF(Minute,InTime,[TimeOut])%60)) TotalHours
The /60 gives you hours, the %60 gives you the remaining minutes, and CONCAT lets you put a colon between them.
/60 为您提供小时数，%60 为您提供剩余的分钟数，而 CONCAT 允许您在它们之间放置一个冒号。
I know it's an old question, but I came across it and thought it might help if someone else comes across it.
我知道这是一个老问题，但我遇到了它，并认为如果其他人遇到它可能会有所帮助。
回答by MAX
If you want 08:30 ( HH:MM) format then try this,
如果你想要 08:30 ( HH:MM) 格式，那么试试这个，
SELECT EmplID
, EmplName
, InTime
, [TimeOut]
, [DateVisited]
, RIGHT('0' + CONVERT(varchar(3),DATEDIFF(minute,InTime, TimeOut)/60),2) + ':' +
RIGHT('0' + CONVERT(varchar(2),DATEDIFF(minute,InTime,TimeOut)%60),2)
as TotalHours from times Order By EmplID, DateVisited
回答by Faraz Ahmed
Please put your related value and try this :
请输入您的相关值并尝试以下操作：
declare @x int, @y varchar(200),
@dt1 smalldatetime = '2014-01-21 10:00:00',
@dt2 smalldatetime = getdate()
set @x = datediff (HOUR, @dt1, @dt2)
set @y = @x * 60 - DATEDIFF(minute,@dt1, @dt2)
set @y = cast(@x as varchar(200)) + ':' + @y
Select @y
回答by Rammer
In case someone is still searching for a query to display the difference in hr min and sec format: (This will display the difference in this format: 2 hr 20 min 22 secs)
如果有人仍在搜索查询以显示 hr min 和 sec 格式的差异：（这将显示以下格式的差异：2 hr 20 min 22 secs）
SELECT
CAST(DATEDIFF(minute, StartDateTime, EndDateTime)/ 60 as nvarchar(20)) + ' hrs ' + CAST(DATEDIFF(second, StartDateTime, EndDateTime)/60 as nvarchar(20)) + ' mins' + CAST(DATEDIFF(second, StartDateTime, EndDateTime)% 60 as nvarchar(20)) + ' secs'
OR can be in the format as in the question:
OR 可以采用问题中的格式：
CAST(DATEDIFF(minute, StartDateTime, EndDateTime)/ 60 as nvarchar(20)) + ':' + CAST(DATEDIFF(second, StartDateTime, EndDateTime)/60 as nvarchar(20))
回答by Carl Nitzsche
Divide the Datediffin MS by the number of ms in a day, cast to Datetime, and then to time:
将Datediffin MS除以一天中的毫秒数，转换为Datetime，然后转换为时间：
Declare @D1 datetime = '2015-10-21 14:06:22.780', @D2 datetime = '2015-10-21 14:16:16.893'
Select Convert(time,Convert(Datetime, Datediff(ms,@d1, @d2) / 86400000.0))
回答by Dhanilan
this would hep you
这会帮助你
DECLARE @DATE1 datetime = '2014-01-22 9:07:58.923'
DECLARE @DATE2 datetime = '2014-01-22 10:20:58.923'
SELECT DATEDIFF(HOUR, @DATE1,@DATE2) ,
DATEDIFF(MINUTE, @DATE1,@DATE2) - (DATEDIFF(HOUR,@DATE1,@DATE2)*60)
SELECT CAST(DATEDIFF(HOUR, @DATE1,@DATE2) AS nvarchar(200)) +
':'+ CAST(DATEDIFF(MINUTE, @DATE1,@DATE2) -
(DATEDIFF(HOUR,@DATE1,@DATE2)*60) AS nvarchar(200))
As TotalHours
