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

As you can see, the result of `DateTime` subtraction is another date, equal to 1900.01.01 plus the resulting difference, which in your case is *1:14:37.367*. — Anton Shepelev, Jun 19 '19 at 14:21

score 128 · Accepted Answer · edited Aug 29 '17 at 12:53

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

Vince Pergolizzi · Answer 2 · 2012-03-01T18:27:48.520

19

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)

edited Mar 01 '12 at 18:27

answered Mar 01 '12 at 18:01

Vince Pergolizzi

6,396
3
19
16

1

@JamesHill Thank you James, you sound like you need a holiday! So I upvoted your answer. – some_bloody_fool Mar 01 '12 at 18:25
@some_bloody_fool, (: thanks. You're probably right actually! – James Hill Mar 01 '12 at 18:26
Ok Ive added another way for you to display it now if you choose to. – Vince Pergolizzi Mar 01 '12 at 18:29

score 13 · Answer 3 · edited Apr 23 '16 at 06:37

13

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

edited Apr 23 '16 at 06:37

Navoneel Talukdar

4,393
5
21
42

answered Aug 29 '13 at 18:40

Ric

131
1
2

2

Wonderful, This is what i am exactly looking for.thanks ! – hakuna Jul 27 '16 at 16:45

score 5 · Answer 4 · answered Oct 07 '15 at 07:01

5

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

answered Oct 07 '15 at 07:01

Sahan Samaranayaka

83
1
8

score 2 · Answer 5 · edited Jun 16 '16 at 11:17

2

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

edited Jun 16 '16 at 11:17

Raviteja

3,399
23
42
69

answered Jun 16 '16 at 10:22

user6474160

21
1

score 0 · Answer 6 · edited Aug 06 '15 at 22:54

0

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.

edited Aug 06 '15 at 22:54

TylerH

20,799
66
75
101

answered Jul 17 '14 at 09:29

tapos ghosh

2,114
23
37

What version of SQL Server supports `TIMEDIFF` function? – Alex Feb 21 '19 at 09:06

score 0 · Answer 7 · edited Dec 14 '14 at 15:00

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

score 0 · Answer 8 · answered Dec 04 '17 at 07:21

0

Below code gives in hh:mm format.

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

answered Dec 04 '17 at 07:21

Nandhakumar S

1
1

1

I think this is not exactly what the OP is asking for. USage of DATE_DIFF should help... – Tyron78 Dec 04 '17 at 07:24

score 0 · Answer 9 · answered Feb 12 '18 at 04:50

0

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

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

answered Feb 12 '18 at 04:50

Kasim Husaini

392
3
14

score 0 · Answer 10 · answered Aug 03 '18 at 02:04

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://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

score 0 · Answer 11 · edited Dec 26 '17 at 07:03

0

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

edited Dec 26 '17 at 07:03

Kols

3,641
2
34
42

answered Mar 01 '12 at 18:00

Sam DeHaan

10,246
2
40
48

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

11 Answers11

Linked

Related