如何根据日期时间计算sql server中的小时数 [英] How to calculate No of Hours in sql server based on date time
本文介绍了如何根据日期时间计算sql server中的小时数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
EMPID EMPNAME logtime type
- < span class =code-comment> ----------------------------------------- ------------------------------------
8 Mohan Varma 2013-10-30 09:26:00。 000 IN
8 Mohan Varma 2013-10-30 09:26:00。 000 OUT
8 Mohan Varma 2013-10-30 09:51:00。 000 IN
8 Mohan Varma 2013-10-30 10:08:00。 000 OUT
8 Mohan Varma 2013-10-30 11:48:00。 000 IN
8 Mohan Varma 2013-10-30 11:五8:00。 000 OUT
8 Mohan Varma 2013-10-30 12:15: 00. 000 IN
8 Mohan Varma 2013-10-30 12:48:00。 000 OUT
8 Mohan Varma 2013-10-30 13:06:00。 000 IN
8 Mohan Varma 2013-10-30 13:19:00。 000 OUT
8 Mohan Varma 2013-10-30 14:03:00。 000 IN
8 Mohan Varma 2013-10-30 15:40:00。 000 OUT
8 Mohan Varma 2013-10-30 15:42:00。 000 IN
< span class =code-digit> 8 Mohan Varma 2013-10-30 16:27:00。 000 OUT
8 Mohan Varma 2013-10-30 16:35:00。 000 IN
8 Mohan Varma 2013-10-30 18:26:00。 000 OUT
8 Mohan Varma 2013-10-30 18:30:00。 000 IN
8 Mohan Varma 2013-10-30 18:35:00。 000 OUT
我必须计算员工入住的小时数此要求(IN / OUT)。任何人都可以建议我如何做到这一点。
解决方案
声明 @ Tab 表(EMPID int ,EMPNAME varchar ( 20 ),logtime datetime , type char ( 5 ))
insert @ Tab
选择 8 ,' Mohan Varma' ,' 2013-10-30 09:26:00.000', IN'
联盟 全部 选择 8 ,' Mohan Varma',' 2013-10-30 09:26:00.000',' OUT'
联盟 全部 选择 8 ,' Mohan Varma',' 2013-10-30 09:51:00.000',' IN'
联盟 全部 选择 8 ,' < span class =code-string> Mohan Varma',' 2013-10-30 10:08:00.000',' OUT'
联盟 全部 选择 8 ,' Mohan Varma',' 2013-10-30 11:48:00.000 ',' IN'
联盟 全部 选择 8 ,' Mohan Varma',' 2013-10-30 11:58:00.000',' OUT'
联盟所有 选择 8 ,' Mohan Varma',' 2013-10-30 12:15:00.000',' IN'全部 选择 8 ,' Mohan Varma',' 2013-10-30 15:40:00.000',' OUT'
联盟 全部 选择 < span class =code-digit> 8 ,' Mohan Varma',' 2013-10-30 12:48:00.000',' OUT'
联盟 全部 选择 8 ,' Mohan Varma',' 2013-10-30 13:06:00.000',' IN'
联盟 全部 选择 8 ,' Mohan Varma',' 2013-10-30 13: 19:00.000',' OUT'
联盟 全部 选择 8 ,' Mohan Varma',' 2013-10-30 14:03:00.000',' IN'
联盟
联盟 全部 选择 8 ,' Mohan Varma' ,' 2013-10-30 15:42:00.000',' IN'
联盟 全部 选择 8 ,' Mohan Varma' ,' 2013-10-30 16:27:00.000',' OUT'
联盟 全部 选择 8 ,' Mohan Varma',' 2013-10- 30 16:35:00.000',' IN'
联盟 所有 选择 8 ,' Mohan Varma',' 2013-10-30 18:26:00.000',' OUT'
联盟 所有 选择 8 ,' Mohan Varma',' < span class =code-string> 2013-10-30 18:30:00.000',' IN'
联盟 全部 选择 8 ,' Mohan Varma',' 2013-10-30 18:35:00.000',' OUT'
选择 EmpId,EmpName,LogTime,Rn = ROW_NUMBER() OVER ( Order 按 LogTime)进入#Te1 从 @ Tab 其中 类型 = ' 在'
选择 EmpId,EmpName,LogTime,Rn = ROW_NUMBER() OVER (订单 按 LogTime)进入#Te2 来自 @ Tab 其中 type = ' Out'
选择 * 从#Te1
选择 * 从#Te2
Decl是 @ Min int
设置 @ Min =(选择 Sum(DATEDIFF(分钟,TE1.LogTime,TE2。 LogTime)) AS 分钟来自#te1 AS TE1
内部 加入#te2 AS TE2
On TE1.rn = TE2.rn)
SELECT CAST(@ Min / 60 AS VARCHAR ( 5 ))+ ' 小时' + ' :' + RIGHT (' 0' + CAST(@ Min%60 AS VARCHAR (< span class =code-digit> 2 )), 2 )+ ' < span class =code-string> Min' AS ' WorkingTime'
Drop 表# TE1
Drop 表 #TE2
试试这个:
选择 CAST
(
( SUM(datepart(hh, convert ( varchar ,logtime, 108 )))+(sum(datepart(mi, convert ( varchar ,logtime, 108 ))) / 60)) AS VARCHAR ( 2 ))
+ ' :' +
CAST
(总和(datepart(mi, convert ( varchar ,logtime, 108 ))) - 60 *(sum(datepart(mi, convert ( varchar ,logtime, 108 )))/ 60)
as VARCHAR ( 2 ))来自 tbl_check 其中 type = ' IN'跨度>
EMPID EMPNAME logtime type
-------------------------------------------------------------------------------
8 Mohan Varma 2013-10-30 09:26:00.000 IN
8 Mohan Varma 2013-10-30 09:26:00.000 OUT
8 Mohan Varma 2013-10-30 09:51:00.000 IN
8 Mohan Varma 2013-10-30 10:08:00.000 OUT
8 Mohan Varma 2013-10-30 11:48:00.000 IN
8 Mohan Varma 2013-10-30 11:58:00.000 OUT
8 Mohan Varma 2013-10-30 12:15:00.000 IN
8 Mohan Varma 2013-10-30 12:48:00.000 OUT
8 Mohan Varma 2013-10-30 13:06:00.000 IN
8 Mohan Varma 2013-10-30 13:19:00.000 OUT
8 Mohan Varma 2013-10-30 14:03:00.000 IN
8 Mohan Varma 2013-10-30 15:40:00.000 OUT
8 Mohan Varma 2013-10-30 15:42:00.000 IN
8 Mohan Varma 2013-10-30 16:27:00.000 OUT
8 Mohan Varma 2013-10-30 16:35:00.000 IN
8 Mohan Varma 2013-10-30 18:26:00.000 OUT
8 Mohan Varma 2013-10-30 18:30:00.000 IN
8 Mohan Varma 2013-10-30 18:35:00.000 OUT
I have to calculate the no of hours that the employee was IN based on this requirement(IN/OUT). Can any one suggest me how to do this.
解决方案
Declare @Tab Table(EMPID int,EMPNAME varchar(20),logtime datetime,type char(5)) insert @Tab Select 8,'Mohan Varma','2013-10-30 09:26:00.000','IN' Union All Select 8,'Mohan Varma','2013-10-30 09:26:00.000','OUT' Union All Select 8,'Mohan Varma','2013-10-30 09:51:00.000','IN' Union All Select 8,'Mohan Varma','2013-10-30 10:08:00.000','OUT' Union All Select 8,'Mohan Varma','2013-10-30 11:48:00.000','IN' Union All Select 8,'Mohan Varma','2013-10-30 11:58:00.000','OUT' Union All Select 8,'Mohan Varma','2013-10-30 12:15:00.000','IN' Union All Select 8,'Mohan Varma','2013-10-30 12:48:00.000','OUT' Union All Select 8,'Mohan Varma','2013-10-30 13:06:00.000','IN' Union All Select 8,'Mohan Varma','2013-10-30 13:19:00.000','OUT' Union All Select 8,'Mohan Varma','2013-10-30 14:03:00.000','IN' Union All Select 8,'Mohan Varma','2013-10-30 15:40:00.000','OUT' Union All Select 8,'Mohan Varma','2013-10-30 15:42:00.000','IN' Union All Select 8,'Mohan Varma','2013-10-30 16:27:00.000','OUT' Union All Select 8,'Mohan Varma','2013-10-30 16:35:00.000','IN' Union All Select 8,'Mohan Varma','2013-10-30 18:26:00.000','OUT' Union All Select 8,'Mohan Varma','2013-10-30 18:30:00.000','IN' Union All Select 8,'Mohan Varma','2013-10-30 18:35:00.000','OUT' Select EmpId,EmpName,LogTime,Rn = ROW_NUMBER() OVER (Order By LogTime) Into #Te1 From @Tab Where type = 'In' Select EmpId,EmpName,LogTime,Rn = ROW_NUMBER() OVER (Order By LogTime) Into #Te2 From @Tab Where type = 'Out' Select * From #Te1 Select * From #Te2 Declare @Min int Set @Min = (Select Sum(DATEDIFF(Minute,TE1.LogTime,TE2.LogTime)) AS Minutes From #te1 AS TE1 inner Join #te2 AS TE2 On TE1.rn = TE2.rn) SELECT CAST( @Min/60 AS VARCHAR(5))+ ' Hrs' + ':'+ RIGHT('0' + CAST( @Min%60 AS VARCHAR(2)), 2)+' Min' AS 'WorkingTime' Drop Table #TE1 Drop Table #TE2
Try this:
select CAST ( (SUM (datepart(hh, convert (varchar, logtime, 108))) +(sum(datepart(mi, convert (varchar, logtime, 108)))/60) )AS VARCHAR(2)) + ':' + CAST (sum(datepart(mi, convert (varchar, logtime, 108))) - 60 * (sum(datepart(mi, convert (varchar, logtime, 108)))/60) as VARCHAR(2))from tbl_check where type='IN'
这篇关于如何根据日期时间计算sql server中的小时数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文