datediff

when I create the table I used

[from_time] [smalldatetime] NULL ,
[to_time] [smalldatetime] NULL ,
[total_hrs] AS (datediff(hh,[from_time],[to_time])) ,

but the hours calculation is comming wrong. moreover i want set the format like hh:mi. but i could not. pls helpRefer to DATEPART function under books online.#

HTH|||elams if your goal is to return elapsed time, you might want to change your calculated field to hold minutes or seconds rather than hours, this would make it easier to re-format.

as an alternative, here is a function that will return elapsed time.

Create function fn_ElapsedTime (
@.starttime datetime,
@.endtime datetime = Null)
returns varchar(40)
as
begin
declare @.d int, @.h int, @.m int, @.s int, @.ms int, @.dif1 int, @.ret varchar(40)
select @.d = 0, @.h = 0, @.m = 0, @.s = 0, @.ms = 0

set @.d = datediff(dd,@.starttime,@.endtime)
set @.dif1 = datediff(ms,dateadd(dd,@.d,@.starttime),@.endtime)

if (@.dif1 > 0) begin
set @.ms = @.dif1 % 1000
set @.dif1 = @.dif1 - @.ms
set @.s = ((@.dif1 / 1000) % 60)
set @.dif1 = @.dif1 - (@.s * 1000)
set @.m = ((@.dif1 / 60000) % 60)
set @.dif1 = @.dif1 - (@.m * 60000)
set @.h = ((@.dif1 / 3600000) % 60)
end

set @.ret = cast(@.d as varchar(25)) + ':' +
right('00' + cast(@.h as varchar(2)),2) + ':' +
right('00' + cast(@.m as varchar(2)),2) + ':' +
right('00' + cast(@.s as varchar(2)),2) + ':' +
right('000' + cast(@.ms as varchar(3)),3)

return @.ret
end

GO

create table #Tmp(
from_time smalldatetime
, to_time smalldatetime
, total_hrs as (datediff(hh,from_time, to_time))
)
insert into #Tmp values(getdate(), dateadd(dd,4,getdate()))
insert into #Tmp values(getdate(), dateadd(hh,2,getdate()))
insert into #Tmp values(getdate(), dateadd(mi,3,getdate()))
select from_time
, to_time
, total_hrs
, dbo.fn_ElapsedTime(from_time,to_time) as Elapsed_Time
From #Tmp|||it works. Thanks mate
elam