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 68

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

Last Update: January 15, 2009 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: 1

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

January 15, 2009 07:22:56

swathi660

Member Since: August 2008 Contribution: 2

select starttime stoptime datediff(day starttime stoptime) as newtime from date.try this...........

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank