如何在sql server 2008中添加两次(如10:30:16,14:27:44) [英] How to add two time(like 10:30:16,14:27:44) in sql server 2008
本文介绍了如何在sql server 2008中添加两次(如10:30:16,14:27:44)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两列如下图所示
InTime OutTime
10:30:16 10:10:11
14:27:44 13:47:55
9:35:01 18:31:51
现在我需要将stime server 2008中的两个输出值之间的intime列值和outtime列值相加,然后进行dofference.Please any一个人帮帮我
OutPut:
Intime Outtime
34:33:01 42:29:57
差异输出:
7:56:56
解决方案
试试这个,你的任务现在是添加秒部分:)
DECLARE @ TBL TABLE
(
intime TIME ,
outtime TIME
)
INSERT INTO @ TBL
SELECT ' 10:30:16',' 10:10:11'
UNION ALL
SELECT ' 14:27:44',' 13:47:55'
UNION ALL
SELECT ' 9:35:01',' 18:31:51'
DECLARE @ InTimeString VARCHAR ( 10 )
DECLARE @OutTimeString VARCHAR ( 10 )
DECLARE @TotalDiffString VARCHAR ( 10 )
DECLARE @ InMilliseconds INT
DECLARE @ OutMilliseconds INT
DECLARE @TotalDiffMillSeconds INT
SELECT @ InMilliseconds = Sum (DATEDIFF(ms,' 00:00:00.000',intime)),
< span class =code-sdkkeyword> @ OutMilliseconds = Sum(DATEDIFF(ms,' 00:00 :00.000',outtime)),
@TotalDiffMillSeconds = Sum(DATEDIFF(ms,' 00:00:00.000',o uttime)) - 总和(DATEDIFF(ms,' 00:00:00.000',intime))
来自 @ TBL
- SELECT CONVERT(VARCHAR,Sum(DATEDIFF(ms,'00:00:00.000',intime) ),)114) - ,DATEADD(s,Sum(DATEDIFF(s,'00:00:00.000',intime)),'00:00:00')
- FROM @TBL
SELECT @ InTimeString = RIGHT (' 0000000000' + CAST(( @ InMilliseconds / 3600000 ) AS VARCHAR ( 10 ) )+ ' :', 3 )
+ RIGHT (' 0000000000' + CAST((( @ InMilliseconds % 3600000 )/ 60000 ) AS VARCHAR ( 10 ))+ ' ', 2 )
SELECT @OutTimeString = RIGHT ( ' 0000000000' + CAST(( @ OutMilliseconds / 3600000 ) AS VARCHAR ( 10 ))+ ' :', 3 )
+ RIGHT (' 0000000000' + CAST((( @ OutMilliseconds % 3600000 )/ 60000 ) AS VARCHAR ( 10 ))+ ' ', 2 )
SELECT @TotalDiffString = RIGHT (' 0000000000' + CAST(( @ TotalDiffMillSeconds / 3600000 ) AS VARCHAR ( 10 ))+ ' :', 3 )
+ RIGHT (' 0000000000' + CAST((( @ TotalDiffMillSeconds % 3600000 )/ 60000 ) AS VARCHAR ( 10 ))+ ' ' , 2 )
SELECT @InTimeString , @OutTimeString , @TotalDiffStr ING 跨度>
Hi,
I have two columns as shown in below
InTime OutTime
10:30:16 10:10:11
14:27:44 13:47:55
9:35:01 18:31:51
Now i need to sum intime column values and outtime column values seperatly then dofference between both output values in sql server 2008.Please any one help me.
OutPut:
Intime Outtime
34:33:01 42:29:57
difference output:
7:56:56
解决方案
Try this, your task is now to add seconds part :)
DECLARE @TBL TABLE ( intime TIME, outtime TIME ) INSERT INTO @TBL SELECT '10:30:16', '10:10:11' UNION ALL SELECT '14:27:44', '13:47:55' UNION ALL SELECT '9:35:01', '18:31:51' DECLARE @InTimeString VARCHAR(10) DECLARE @OutTimeString VARCHAR(10) DECLARE @TotalDiffString VARCHAR(10) DECLARE @InMilliseconds INT DECLARE @OutMilliseconds INT DECLARE @TotalDiffMillSeconds INT SELECT @InMilliseconds = Sum(DATEDIFF(ms, '00:00:00.000', intime)), @OutMilliseconds = Sum(DATEDIFF(ms, '00:00:00.000', outtime)), @TotalDiffMillSeconds = Sum(DATEDIFF(ms, '00:00:00.000', outtime)) - Sum(DATEDIFF(ms, '00:00:00.000', intime)) from @TBL --SELECT CONVERT(VARCHAR, Sum(DATEDIFF(ms, '00:00:00.000', intime)), 114) --, DATEADD(s, Sum(DATEDIFF(s, '00:00:00.000', intime)), '00:00:00') --FROM @TBL SELECT @InTimeString = RIGHT('0000000000' + CAST((@InMilliseconds / 3600000) AS VARCHAR(10)) + ':', 3) + RIGHT('0000000000' + CAST(((@InMilliseconds % 3600000) / 60000) AS VARCHAR(10)) + '',2) SELECT @OutTimeString = RIGHT('0000000000' + CAST((@OutMilliseconds / 3600000) AS VARCHAR(10)) + ':', 3) + RIGHT('0000000000' + CAST(((@OutMilliseconds % 3600000) / 60000) AS VARCHAR(10)) + '',2) SELECT @TotalDiffString = RIGHT('0000000000' + CAST((@TotalDiffMillSeconds / 3600000) AS VARCHAR(10)) + ':', 3) + RIGHT('0000000000' + CAST(((@TotalDiffMillSeconds % 3600000) / 60000) AS VARCHAR(10)) + '',2) SELECT @InTimeString, @OutTimeString, @TotalDiffString
这篇关于如何在sql server 2008中添加两次(如10:30:16,14:27:44)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文