diff b/w two dates

[reddy]

Hi,
i have 3 fields: start_inspect_datetime, end_inspect_datetime, Diag_Hrs.
so i want to get the difference of start and end datetime=Diag_Hrs.
here i am using the below stored proc.
but i am getting only the hours or minutes or seconds.
so how to get the hours(if diff>59 mins),minutes(if diff>59 sec),seconds.
for Ex: here diff=185 sec. then Diag_hrs should be 3 hours,0 mins, 5 secs.
so how we'll get this.
pl help me out asap.
Thanx
reddy

select Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime) from asset_diag_trans_table
--where Gpc_no=@GPC_no


--select Asset_Diag_Hrs=(datediff(mm,start_inspect_datetime ,end_inspect_datetime)) from asset_diag_trans_table
-- where Gpc_no=@GPC_no

select Asset_Diag_Seconds=(datediff(ss,start_inspect_date time,end_inspect_datetime) ) from asset_diag_trans_table
where Gpc_no=@GPC_no

[Peter Main]

Firstly date part mm stands for months not minutes.

Maybe the following code will help.

DECLARE @Asset_Diag_Hrs INT,@Asset_Diag_Mins INT,@Asset_Diag_Seconds INT

select @Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime)
from asset_diag_trans_table
where Gpc_no=@GPC_no

select @Asset_Diag_Mins=(datediff(mi,start_inspect_dateti me,end_inspect_datetime))-
(@Asset_Diag_Hrs*60)
from asset_diag_trans_table
where Gpc_no=@GPC_no

select @Asset_Diag_Seconds=(datediff(ss,start_inspect_dat etime,end_inspect_datetime))-
(@Asset_Diag_Hrs*3600)- (@Asset_Diag_Mins*60)
from asset_diag_trans_table
where Gpc_no=@GPC_no


------------
reddy at 12/11/00 10:04:55 AM

Hi,
i have 3 fields: start_inspect_datetime, end_inspect_datetime, Diag_Hrs.
so i want to get the difference of start and end datetime=Diag_Hrs.
here i am using the below stored proc.
but i am getting only the hours or minutes or seconds.
so how to get the hours(if diff>59 mins),minutes(if diff>59 sec),seconds.
for Ex: here diff=185 sec. then Diag_hrs should be 3 hours,0 mins, 5 secs.
so how we'll get this.
pl help me out asap.
Thanx
reddy

select Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime) from asset_diag_trans_table
--where Gpc_no=@GPC_no


--select Asset_Diag_Hrs=(datediff(mm,start_inspect_datetime ,end_inspect_datetime)) from asset_diag_trans_table
-- where Gpc_no=@GPC_no

select Asset_Diag_Seconds=(datediff(ss,start_inspect_date time,end_inspect_datetime) ) from asset_diag_trans_table
where Gpc_no=@GPC_no

[reddy]

Hi
how to club the 3 results(hours,mins,sec)ie 3 variables in one field(asset_diag_hrs) here.
thanx
reddy


------------
Peter Main at 12/11/00 10:35:46 AM

Firstly date part mm stands for months not minutes.

Maybe the following code will help.

DECLARE @Asset_Diag_Hrs INT,@Asset_Diag_Mins INT,@Asset_Diag_Seconds INT

select @Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime)
from asset_diag_trans_table
where Gpc_no=@GPC_no

select @Asset_Diag_Mins=(datediff(mi,start_inspect_dateti me,end_inspect_datetime))-
(@Asset_Diag_Hrs*60)
from asset_diag_trans_table
where Gpc_no=@GPC_no

select @Asset_Diag_Seconds=(datediff(ss,start_inspect_dat etime,end_inspect_datetime))-
(@Asset_Diag_Hrs*3600)- (@Asset_Diag_Mins*60)
from asset_diag_trans_table
where Gpc_no=@GPC_no


------------
reddy at 12/11/00 10:04:55 AM

Hi,
i have 3 fields: start_inspect_datetime, end_inspect_datetime, Diag_Hrs.
so i want to get the difference of start and end datetime=Diag_Hrs.
here i am using the below stored proc.
but i am getting only the hours or minutes or seconds.
so how to get the hours(if diff>59 mins),minutes(if diff>59 sec),seconds.
for Ex: here diff=185 sec. then Diag_hrs should be 3 hours,0 mins, 5 secs.
so how we'll get this.
pl help me out asap.
Thanx
reddy

select Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime) from asset_diag_trans_table
--where Gpc_no=@GPC_no


--select Asset_Diag_Hrs=(datediff(mm,start_inspect_datetime ,end_inspect_datetime)) from asset_diag_trans_table
-- where Gpc_no=@GPC_no

select Asset_Diag_Seconds=(datediff(ss,start_inspect_date time,end_inspect_datetime) ) from asset_diag_trans_table
where Gpc_no=@GPC_no

[reddy]

Hi
how to club the 3 results(hours,mins,sec)ie 3 variables in one field(asset_diag_hrs) here.
thanx
reddy


------------
Peter Main at 12/11/00 10:35:46 AM

Firstly date part mm stands for months not minutes.

Maybe the following code will help.

DECLARE @Asset_Diag_Hrs INT,@Asset_Diag_Mins INT,@Asset_Diag_Seconds INT

select @Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime)
from asset_diag_trans_table
where Gpc_no=@GPC_no

select @Asset_Diag_Mins=(datediff(mi,start_inspect_dateti me,end_inspect_datetime))-
(@Asset_Diag_Hrs*60)
from asset_diag_trans_table
where Gpc_no=@GPC_no

select @Asset_Diag_Seconds=(datediff(ss,start_inspect_dat etime,end_inspect_datetime))-
(@Asset_Diag_Hrs*3600)- (@Asset_Diag_Mins*60)
from asset_diag_trans_table
where Gpc_no=@GPC_no


------------
reddy at 12/11/00 10:04:55 AM

Hi,
i have 3 fields: start_inspect_datetime, end_inspect_datetime, Diag_Hrs.
so i want to get the difference of start and end datetime=Diag_Hrs.
here i am using the below stored proc.
but i am getting only the hours or minutes or seconds.
so how to get the hours(if diff>59 mins),minutes(if diff>59 sec),seconds.
for Ex: here diff=185 sec. then Diag_hrs should be 3 hours,0 mins, 5 secs.
so how we'll get this.
pl help me out asap.
Thanx
reddy

select Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime) from asset_diag_trans_table
--where Gpc_no=@GPC_no


--select Asset_Diag_Hrs=(datediff(mm,start_inspect_datetime ,end_inspect_datetime)) from asset_diag_trans_table
-- where Gpc_no=@GPC_no

select Asset_Diag_Seconds=(datediff(ss,start_inspect_date time,end_inspect_datetime) ) from asset_diag_trans_table
where Gpc_no=@GPC_no