Sql中两次之间的时间差异 [英] Time differnce between two time in 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.';

这篇关于Sql中两次之间的时间差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！