How to compare two dates to find time difference in SQL Server 2005, date manipulation

Question

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?

I tried this:

select    (job_end - job_start) from tableA 

but ended up with this:

1900-01-01 01:14:37.367 

Answer 1

Take a look at the DateDiff() function.

-- 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

Answer 2

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

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).

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

Here is the code to do that for both SQL Server 2005 and 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)