如何比较两个日期在SQL Server 2005中的时间差异，日期操作?

I have two columns:

我有两个列:

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

How would it be possible using T-SQL to find the raw amount of time that has passed between when the job started and when the job ended?

如何使用T-SQL找到工作开始时和工作结束时之间的原始时间?

I tried this:

我试着这样的:

select    (job_end - job_start) from tableA

but ended up with this:

但结果是:

1900-01-01 01:14:37.367

10 个解决方案

#1

110

Take a look at the DateDiff() function.

查看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
...

You can see it in action / play with it here

你可以在这里看到它。

#2

You can use the DATEDIFF function to get the difference in minutes, seconds, days etc.

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

SELECT DATEDIFF(MINUTE,job_start,job_end)

MINUTE obviously returns the difference in minutes, you can also use DAY, HOUR, SECOND, YEAR (see the books online link for the full list).

分钟明显地返回差值，你也可以使用DAY, HOUR, SECOND, YEAR(见书目在线链接的完整列表)。

If you want to get fancy you can show this differently for example 75 minutes could be displayed like this: 01:15:00:0

如果你想要得到想象，你可以用不同的方式来展示，例如75分钟可以像这样显示:01:15 00:0。

Here is the code to do that for both SQL Server 2005 and 2008

这是为SQL Server 2005和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)

#3

Cast the result as TIME and the result will be in time format for duration of the interval.

将结果转换为时间，结果将在间隔期间以时间格式进行。

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

#4

I think you need the time gap between job_start & job_end.

我认为你需要在job_start和job_end之间的时间间隔。

Try this...

试试这个…

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

I ended up with this.

我最终得到了这个。

01:14:37

#5

If your database StartTime = 07:00:00 and endtime = 14:00:00, and both are time type. Your query to get the time difference would be:

如果您的数据库StartTime = 07:00:00和endtime = 14:00:00，两者都是时间类型。你的查询可以得到时差:

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

If your database startDate = 2014-07-20 07:00:00 and endtime = 2014-07-20 23:00:00, you can also use this query.

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

#6

Declare the Start and End date DECLARE @SDATE AS DATETIME

声明开始和结束日期声明@SDATE为DATETIME。

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

-- Get the Result in HH:MI:SS:MMM(24H) format SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, @START_DATE, @END_DATE), 0), 114) AS TimeDiff

-得到结果:MI: MMM(24H)格式选择CONVERT(VARCHAR(12)， DATEADD(MS, DATEDIFF(MS， @START_DATE， @END_DATE)， 0)， 114)作为TimeDiff。

#7

Try this in Sql Server

在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

#8

Below code gives in hh:mm format.

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

select RIGHT(LEFT(job_end- job_start,17),5)

选择正确的(左(job_end job_start,17),5)

#9

Take a look at DATEDIFF, this should be what you're looking for. It takes the two dates you're comparing, and the date unit you want the difference in (days, months, seconds...)

看看DATEDIFF，这应该是您正在寻找的。它需要你比较的两个日期，以及你想要的日期单位(天，月，秒…)

#10

I used following logic and it worked for me like marvel:

我用了下面的逻辑，它对我来说就像奇迹一样:

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

#1

110