在SQL Server中获取Exact计算的总小时数和分钟数 [英] Get Exact calculated total Hours and minutes in sql server

查看:183
本文介绍了在SQL Server中获取Exact计算的总小时数和分钟数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

嗨!美好的一天。我正在使用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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆