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

sql sql-server-2005 date datediff
some_bloody_fool picture some_bloody_fool · Mar 1, 2012 · Viewed 360.5k times · Source

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

James Hill picture James Hill · Mar 1, 2012

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

Related questions

SQL Server 2005: how to subtract 6 month

I have a date, suppose today date declare @d datetime set @d = '20101014' I need select @d - <six month> where is the real number of days that contains last six month, beginning from @d.

Read More
Get the week start date and week end date from week number

I have a query that counts member's wedding dates in the database. SELECT SUM(NumberOfBrides) AS [Wedding Count] , DATEPART( wk, WeddingDate) AS [Week Number] , DATEPART( year, WeddingDate) AS [Year] FROM MemberWeddingDates GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate) ORDER …

Read More
Converting JDE Julian date to Gregorian

I'm trying to convert JDE dates, and have amassed a large quantity of information and figured I'd try to do an SQL conversion function to simplify some tasks. Here's the function I came up with, which I simply call "ToGregorian" …

Read More