Performing date/time subtraction in SQL Server

Question

I have four columns in a table:

date entered, time entered, date completed, time completed

I would like to know the difference between date/time ENTERED and date/time COMPLETED

For example

date entered = 1/1/2001
time entered = 10:00
time completed = 1/2/2001
time completed = 11:00

The difference is 25 hours.

How can I perform this computation with a select statement?

I tried this:

DATEDIFF(hh,dateadd(hh,[Time Entered],[Date Entered]),dateadd(hh,[Time Completed],[Date Completed]) ) AS [Hours]

and got the following error:

Msg 8116, Level 16, State 1, Line 2
Argument data type time is invalid for argument 2 of dateadd function.

This topic is covered here, too. http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server — Griffin, Mar 15 '12 at 00:24

Mitch Wheat · Accepted Answer · 2012-03-15T00:07:13.340

5

Declare @dateentered date = '20010101'
Declare @timeentered time = '10:00' 
Declare @datecompleted date = '20010102' 
Declare @timecompleted time = '11:00'

select datediff(hh, @dateentered + cast(@timeentered as datetime), 
                    @datecompleted + cast(@timecompleted as datetime))

So, in terms of your tables' columns:

select datediff(hh, [date entered] + cast([time entered] as datetime), 
                    [date completed] + cast([time completed] as datetime)) as [Hours]

edited Mar 15 '12 at 00:07

answered Mar 14 '12 at 23:59

Mitch Wheat

295,962
43
465
541

+1 This is the correct answer, although I would have casted the "date" field to a datetime instead of the "time". – Griffin Mar 15 '12 at 00:22

score 3 · Answer 2 · answered Mar 14 '12 at 23:38

3

select datediff(hour,'1/1/2001 10:00','1/2/2001 11:00')

answered Mar 14 '12 at 23:38

Thit Lwin Oo

3,388
3
20
23

Phil · Answer 3 · 2012-03-14T23:51:05.243

1

Try

select DateDiff(ss, [Date Entered] + convert(datetime, [Time Entered]), 
                    [Date Completed] + convert(datetime, [Time Completed])) 
from myTable

to get the result in seconds.

Here's a standalone example:

declare @dateentered date = '1/1/2001'
declare @timeentered time = '10:00'
declare @datecompleted date = '1/2/2001'
declare @timecompleted time = '11:00'

select DateDiff(ss, 
    @dateentered + convert(datetime, @timeentered), 
    @datecompleted + convert(datetime, @timecompleted))

And of course you can specify different dateparts as specified for DATEDIFF.

edited Mar 14 '12 at 23:51

answered Mar 14 '12 at 23:39

Phil

42,255
9
100
100

updated my answer to one that works (if your columns are date or datetime) – Phil Mar 14 '12 at 23:46
Operand data type date is invalid for add operator. – Alex Gordon Mar 14 '12 at 23:47
Fixed since you're using date and time types. – Phil Mar 14 '12 at 23:48

score 0 · Answer 4 · answered Mar 14 '12 at 23:38

0

Use DateDiff

answered Mar 14 '12 at 23:38

Coltech

1,670
3
16
31

thank you! i just updated my question can you take a look please – Alex Gordon Mar 14 '12 at 23:45

score 0 · Answer 5 · answered Mar 14 '12 at 23:39

DECLARE @StartDate DATETIME
Declare @EndDate DATETIME
declare @startime datetime
declare @endime datetime

SET @StartDate = '2001-01-01'
set @startime = '10:00'
SET @EndDate = '2001-01-02'
set @endime = '11:00'

set @StartDate = @StartDate + @startime
set @EndDate = @EndDate + @endime

--To get only Hours
SELECT DATEDIFF(hh, @StartDate,@EndDate ) AS [Hours];

Performing date/time subtraction in SQL Server

5 Answers5