计算两个时间相差几小时几分几秒

本文将介绍如何利用 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

执行结果如下:

image

【参考数据】

  • [SQL]比较2个时间差多少个小时、分、秒
  • CAST 和 CONVERT (Transact-SQL)