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.

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

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

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

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'

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

select DATEDIFF(MI,FromTime,ToTime) From ShiftMaster  -- between no of minutes