如何比较两个日期以查找 SQLServer2005中的时间差，日期操作

我有两个专栏:

job_start                         job_end
2011-11-02 12:20:37.247           2011-11-02 13:35:14.613

如何使用 T-SQL 查找作业开始和结束之间的原始时间量？

我试过了:

select    (job_end - job_start) from tableA

结果却是这样:

1900-01-01 01:14:37.367
371325 次浏览
小开

看看 DATEDIFF，这应该就是你要找的。它需要你比较的两个日期，和你想要差异的日期单位(天，月，秒...)

小开
最佳答案

看一下 DateDiff()函数。

-- Syntax
-- DATEDIFF ( datepart , startdate , enddate )


-- Example usage
SELECT DATEDIFF(DAY, GETDATE(), GETDATE() + 1) AS DayDiff
SELECT DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1) AS MinuteDiff
SELECT DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) AS SecondDiff
SELECT DATEDIFF(WEEK, GETDATE(), GETDATE() + 1) AS WeekDiff
SELECT DATEDIFF(HOUR, GETDATE(), GETDATE() + 1) AS HourDiff
...

你可以看到它的行动/在这里玩

小开

您可以使用 DATEDIFF函数获得分钟、秒、天等的差异。

SELECT DATEDIFF(MINUTE,job_start,job_end)

分钟显然返回分钟的差异，你也可以使用日，小时，第二，年(见图书在线链接的完整列表)。

如果你想变得花哨，你可以用不同的方式显示，例如75分钟可以像这样显示: 01:15:00:0

下面是用于 SQLServer2005和2008的代码

-- SQL Server 2005
SELECT CONVERT(VARCHAR(10),DATEADD(MINUTE,DATEDIFF(MINUTE,job_start,job_end),'2011-01-01 00:00:00.000'),114)


-- SQL Server 2008
SELECT CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,job_start,job_end),'2011-01-01 00:00:00.000') AS TIME)
小开

将结果强制转换为 TIME，并且结果将在时间间隔内以时间格式显示。

select CAST(job_end - job_start) AS TIME(0)) from tableA
小开

如果数据库 StartTime = 07:00:00和 endtime = 14:00:00都是 time 类型。你要求得到时差的问题是:

SELECT TIMEDIFF(Time(endtime ), Time(StartTime )) from tbl_name

如果数据库 startDate = 2014-07-20 07:00:00和 endtime = 2014-07-20 23:00:00，也可以使用此查询。

小开

在 Sql Server 中尝试这样做

SELECT
start_date as firstdate,end_date as seconddate
,cast(datediff(MI,start_date,end_date)as decimal(10,3)) as minutediff
,cast(cast(cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60) as int ) as varchar(10)) + ' ' + 'Days' + ' '
+ cast(cast((cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60) -
floor(cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60)) ) * 24 as int) as varchar(10)) + ':'


+ cast( cast(((cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60)
- floor(cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60)))*24
-
cast(floor((cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60)
- floor(cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60)))*24) as decimal)) * 60 as int) as varchar(10))


FROM [AdventureWorks2012].dbo.learndate
小开

我认为你需要 作业开始和作业结束之间的时间间隔。

试试这个..。

select SUBSTRING(CONVERT(VARCHAR(20),(job_end - job_start),120),12,8) from tableA

我得到了这个。

01:14:37
小开

声明开始和结束日期 DECLARE @SDATE AS DATETIME

TART_DATE  AS DATETIME
DECLARE @END_-- Set Start and End date
SET @START_DATE = GETDATE()
SET @END_DATE    = DATEADD(SECOND, 3910, GETDATE())

--以 HH: MI: SS: MMM (24H)格式获得结果 SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, @START_DATE, @END_DATE), 0), 114) AS TimeDiff

小开

下面的代码以 hh: mm 格式给出。

选择 RIGHT (LEFT (job _ end-job _ start，17) ，5)

小开

我使用了下面的逻辑，它像惊奇一样为我工作:

CONVERT(TIME, DATEADD(MINUTE, DATEDIFF(MINUTE, AP.Time_IN, AP.Time_OUT), 0))
小开

如果您试图以一定的精度获得工作时间，请尝试这样做(在 SQLServer2016中进行了测试)

SELECT DATEDIFF(MINUTE,job_start, job_end)/60.00;

DATEDIFF 的各种功能包括:

SELECT DATEDIFF(year,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

档号: https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017


提交答案