Sql中两次之间的时间差异

本文介绍了Sql中两次之间的时间差异的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两列数据类型时间从时间到时间 我想计算总数这两列之间的时间戳 例如

I have two columns with data type Time Fromtime and To time I would like to calculate total timestamp between this two columns eg

InTime OutTime 10:00:00.0000000 12:30:00.0000000

我希望区别为2.5小时

I would like the difference as 2.5 hours

CONVERT(NUMERIC(18, 2), SUM( DATEDIFF(MINUTE,Intime,OutTime)) / 60 + (SUM( DATEDIFF(MINUTE,Intime,OutTime)) % 60) / 100.0) as Hours

这个我得到的结果为2.30小时

with this I am getting result as 2.30 hrs

推荐答案

尝试： Try: SELECT CONVERT(NUMERIC(18,2), DATEDIFF(MINUTE, InTime, OutTime)) / 60 AS Hours FROM MyTable

DECLARE @articleDT DATETIME; DECLARE @nowDate DATETIME; -- Time of the ARTICLE created SET @articleDT = '2012-04-01 08:10:16'; -- Simulation of NOW datetime -- (in real world you would probably use GETDATE()) SET @nowDate = '2012-04-10 11:35:36'; -- Created 9 days ago. SELECT 'Created ' + CAST(DATEDIFF(day, @articleDT, @nowDate) AS NVARCHAR(50)) + ' days ago.'; -- Created 1 weeks, 2 days, 3 hours, 25 minutes and 20 seconds ago. SELECT 'Created ' + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 / 7 AS NVARCHAR(50)) + ' weeks, ' + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 % 7 AS NVARCHAR(50)) + ' days, ' + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 % 24 AS NVARCHAR(50)) + ' hours, ' + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 % 60 AS NVARCHAR(50)) + ' minutes and ' + CAST(DATEDIFF(second, @articleDT, @nowDate) % 60 AS NVARCHAR(50)) + ' seconds ago.';