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

User · Answer

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)

User · Answer

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

User · Answer

Below code gives in hh mm format   select RIGHT LEFT job end- job start 17  5

User · Answer

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

User · Answer

If you trying to get worked hours with some accuracy  try this  tested in SQL Server 2016   SELECT DATEDIFF MINUTE job start  job end  60 00    Various DATEDIFF functionalities are   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      Ref  https   docs microsoft com en-us sql t-sql functions datediff-transact-sql view sql-server-2017

User · Answer

I used following logic and it worked for me like marvel   CONVERT TIME  DATEADD MINUTE  DATEDIFF MINUTE  AP Time IN  AP Time OUT   0

User · Answer

Declare the Start and End date 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      -- 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

User · Answer

Try this in 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

User · Answer

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

User · Answer

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

User · Answer

I think you need the time gap between job start  amp  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

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

Examples related to sql

Examples related to sql-server-2005

Examples related to date

Examples related to datediff