SQL Server - I have a table in sql server with two fields named 'start_time' and 'stop

GeekInterview.com > Tech FAQs > SQL Server

Go To First | Previous Question | Next Question

SQL Server | Question 2 of 55

I have a table in sql server with two fields named 'start_time' and 'stop_time'. both fields have datatype set as 'datetime'. now I want to subtract the 'start_time' from 'stop_time'? I have tried datediff() fuction,but no use. I want the correct outpput of date as well as time .
Need help as early as possible.

Total Answers and Comments: 4

Last Update: June 02, 2008 Asked by: mun25_khanna

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

November 21, 2006 04:35:17

sathyavision

Member Since: November 2006 Contribution: 1

RE: I have a table in sql server with two fields named...

Hi...

Try this

select convert(varchar, datediff(hour, start_time, stop_time) / 24)+
' Days ' +
convert(varchar, datediff(hour, start_time, stop_time) % 24) + ' Hrs '

from tbl_name

Is this answer useful? Yes | No

April 09, 2007 02:50:30

MSenthil_DBA

Member Since: April 2007 Contribution: 1

RE: I have a table in sql server with two fields named...

Hi,

I don't know the problem, try this one

select datediff(hh, start_time, stop_time) from table

gives difference hours between two time

Is this answer useful? Yes | No

May 11, 2007 01:30:04

goldjoy

RE: I have a table in sql server with two fields named...

declare @starttime datetime
declare @timediff bigint
set @starttime='11-May-2007'
SELECT @timediff = DATEDIFF(millisecond, @starttime, getdate())
select @timediff%1000 --for ml sec part
select (@timediff/1000)%60 --for sec part
select (@timediff/(1000*60))%60 --for min part
select (@timediff/(1000*3600))%60 --for hour part

and concatenate the results

Is this answer useful? Yes | No

June 02, 2008 12:19:16

avbhaskar

Member Since: May 2008 Contribution: 2

RE: I have a table in sql server with two fields named 'start_time' and 'stop_time'. both fields have datatype set as 'datetime'. now I want to subtract the 'start_time' from 'stop_time'? I have tried datediff() fuction,but no use. I want the correct outp

Try this example:

declare @startdatetime datetime
declare @stopdatetime datetime
declare @diffdays int
declare @diffhours int
declare @diffmins int
-- assumption is startdate <= stopdate always
SET @startdatetime = '2008-06-01 00:00:00.00'
SET @stopdatetime = '2008-06-02 11:35:43.793'

SET @diffdays = DATEDIFF(dd,@startdatetime,@stopdatetime)
SET @diffhours = (DATEDIFF(hh,@startdatetime,@stopdatetime) - @diffdays*24)
SET @diffmins = (DATEDIFF(mi,@startdatetime,@stopdatetime)- @diffhours*60-@diffdays*24*60)

SELECT @startdatetime 'start-date', @stopdatetime 'stop-date',
CONVERT(VARCHAR(4), @diffdays) + ' DAY(S) ' + CONVERT(VARCHAR(4), @diffhours) + ' HOUR(S) ' + CONVERT(VARCHAR(4), @diffmins)
+ ' MINUTE(S) ' as 'runtime'

Is this answer useful? Yes | No

Go To Top