本文將介紹如何利用 T-SQL 計算兩個時間相差幾小時幾分幾秒。
在論壇上看到有網友問到要計算員工的遲到早退的問題，看起來不是很難但一下子腦筋轉不過來，筆者 TRY 了好久又沒想到要去問 GOOGLE 大神，硬擠出下列的笨方法來湊出結果。
1: use master
2: go
3:
4: declare @t1 table
5: (Y char(4)
6: ,M varchar(2)
7: ,D varchar(2)
8: ,H char(2)
9: ,Mi char(2)
10: ,S char(2)
11: ,cardno char(2)
12: ,name char(1)
13: )
14:
15: insert into @t1 values
16: ('2012', '2', '1', '07', '08', '40', '01', 'A')
17: ,('2012', '2', '1', '07', '09', '40', '02', 'B')
18: ,('2012', '2', '1', '07', '08', '45', '01', 'A')
19: ,('2012', '2', '1', '07', '09', '50', '02', 'B')
20: ,('2012', '2', '1', '08', '10', '45', '03', 'C')
21: ,('2012', '2', '1', '08', '08', '40', '03', 'C')
22: --以下為計算是否早退所用的測試資料
23: ,('2012', '2', '1', '17', '08', '45', '01', 'A')
24: ,('2012', '2', '1', '16', '09', '50', '02', 'B')
25: ,('2012', '2', '1', '15', '15', '45', '03', 'C')
26:
27: select dd
28: ,MIN(tt) as intime --簽到時間
29: ,MAX(tt) as outime --簽退時間
30: ,cardno,name
31: ,case when MIN(tt) > '08:00:00' and MIN(tt) < '17:00' then DATEADD(HH,-8,MIN(tt))
32: else null end as late --遲到時間
33: ,case when MAX(tt) < '17:00' then
34: CONVERT(varchar,DATEDIFF(HOUR,MAX(tt),'17:00:00')-1)
35: + ':'
36: + CONVERT(varchar,60-(DATEPART(MINUTE,MAX(tt))))
37: + ':'
38: + CONVERT(varchar,60-(DATEPART(SECOND,MAX(tt))))
39: else null end as early --早退時間
40: from
41: (
42: select CONVERT(date,Y+RIGHT('0' + M,2)+RIGHT('0' + D,2)) as dd
43: ,CONVERT(time,H+':' +Mi+':' + S) as tt
44: ,cardno,name
45: from @t1 t1
46: ) t1
47: group by dd,cardno,name
後來跟 Rainmaker 大討論，原來可以利用 CONVERT 函數來轉型，搭配 108 這個 style 就可以精簡的做出計算兩個時間相差幾小時幾分幾秒了，程式碼修正如下：
1: use master
2: go
3:
4: declare @t1 table
5: (Y char(4)
6: ,M varchar(2)
7: ,D varchar(2)
8: ,H char(2)
9: ,Mi char(2)
10: ,S char(2)
11: ,cardno char(2)
12: ,name char(1)
13: )
14:
15: insert into @t1 values
16: ('2012', '2', '1', '07', '08', '40', '01', 'A')
17: ,('2012', '2', '1', '07', '09', '40', '02', 'B')
18: ,('2012', '2', '1', '07', '08', '45', '01', 'A')
19: ,('2012', '2', '1', '07', '09', '50', '02', 'B')
20: ,('2012', '2', '1', '08', '10', '45', '03', 'C')
21: ,('2012', '2', '1', '08', '08', '40', '03', 'C')
22: --以下為計算是否早退所用的測試資料
23: ,('2012', '2', '1', '17', '08', '45', '01', 'A')
24: ,('2012', '2', '1', '16', '09', '50', '02', 'B')
25: ,('2012', '2', '1', '15', '15', '45', '03', 'C')
26:
27: select dd
28: ,MIN(tt) as intime --簽到時間
29: ,MAX(tt) as outime --簽退時間
30: ,cardno,name
31: ,case when MIN(tt) > '08:00:00' and MIN(tt) < '17:00'
32: then CONVERT(varchar, DATEADD(s, DATEDIFF(s,'08:00:00',MIN(tt)), 0), 108)
33: else null end as late --遲到時間
34: ,case when MAX(tt) < '17:00'
35: then CONVERT(varchar, DATEADD(s, DATEDIFF(s,MAX(tt),'17:00:00'), 0), 108)
36: else null end as early --早退時間
37: from
38: (
39: select CONVERT(date,Y+RIGHT('0' + M,2)+RIGHT('0' + D,2)) as dd
40: ,CONVERT(time,H+':' +Mi+':' + S) as tt
41: ,cardno,name
42: from @t1 t1
43: ) t1
44: group by dd,cardno,name
執行結果如下：
【參考資料】