在SQL Server中获取Exact计算的总小时数和分钟数 [英] Get Exact calculated total Hours and minutes in sql server
问题描述
嗨!美好的一天。我正在使用ms sql server 2005 express和heres我的表结构:
USER | Log_Date | Log_In_Time | Log_Out_Time |
- -------------------- -------------------------------------------------- --------
xxx | 2013-10-02 23:00:31.623 | 2013-10-02 23:07:31.623 | 2013-10-03 01:08:31.623 |
注意:
LOG_OUT_TIME 是在 10月3日的其他日子,1:08 AM , LOG_IN_TIME 10月2日晚上11点
现在我想添加专栏: TOTAL
它看起来像这样:
| TOTAL |
- -----
| 2. 08 |
有人知道吗?
谢谢你提前!
你可以使用DATEDIFF作为
SELECT DATEDIFF(HOUR,Log_In_Time,Log_OUT_Time)
或
SELECT DATEDIFF(mi,Log_In_Time,Log_OUT_Time)/ 60;
请参阅
http://technet.microsoft.com/en-us/library/ms189794% 28v = sql.90%29.aspx [ ^ ]
尝试:
SELECT CAST(DATEDIFF(mi,Log_In_time,Log_Out_Time) AS FLOAT ) / 60 AS 总 FROM MYTable
但请注意 - 您的示例不会生成您显示的数字!
声明 @ Time int , @ LoginTime datetime = ' 2013-10-02 23:00:00', @ LogoutTime datetime = ' 2013-10- 03 01:08:00'
设置 @ Time = DATEDIFF(分钟, @ LoginTime , @ LogoutTime )
SELECT CAST(@ Time / 60 AS VARCHAR ( 5 ))+ ' Hrs' + ' :' + RIGHT (' 0' + CAST(@ Time%60 AS VARCHAR ( 2 )), 2 )+ ' Min' AS ' WorkingTime'
Hi! Good day. Im using ms sql server 2005 express and heres my table structure:
USER | Log_Date | Log_In_Time | Log_Out_Time |
--------------------------------------------------------------------------------
xxx | 2013-10-02 23:00:31.623|2013-10-02 23:07:31.623|2013-10-03 01:08:31.623|
NOTE:
LOG_OUT_TIME is in other day which is October 3, 1:08 AM , LOG_IN_TIME is October 2, 11:00 PM
Now I want to add column: TOTAL
and it look like this:
|TOTAL|
-------
|2.08 |
Can anybody know the way?
Thank you in advance!
You can use DATEDIFF as
SELECT DATEDIFF(HOUR,Log_In_Time,Log_OUT_Time)
or
SELECT DATEDIFF(mi,Log_In_Time,Log_OUT_Time)/60;
Plese refer
http://technet.microsoft.com/en-us/library/ms189794%28v=sql.90%29.aspx[^]
Try:
SELECT CAST(DATEDIFF(mi, Log_In_time, Log_Out_Time) AS FLOAT)/60 AS Total FROM MYTable
But do note - you sample doesn't generate the number you show!
Declare @Time int,@LoginTime datetime = '2013-10-02 23:00:00',@LogoutTime datetime = '2013-10-03 01:08:00' Set @Time = DATEDIFF(Minute,@LoginTime,@LogoutTime) SELECT CAST( @Time/60 AS VARCHAR(5))+ ' Hrs' + ':'+ RIGHT('0' + CAST( @Time%60 AS VARCHAR(2)), 2)+' Min' AS 'WorkingTime'
这篇关于在SQL Server中获取Exact计算的总小时数和分钟数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!