PDA

View Full Version : ایجاد یک تابعی در sql برای به دست آوردن اختلاف زمان



memo_mojtab
دوشنبه 23 خرداد 1390, 16:54 عصر
سلام
دوستان من یک تابع تو sqlمیخواستم که دو زمان رو بگیره واختلاف آنها را به دست بیاورد؟دو پارامتر ورودی از نوع datetime میباشد!البته تابع زیر رو خودم نوشتم
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER FUNCTION [dbo].[fn_getTimeDiffrent](@inputTime Datetime,@OutPutTime Datetime)
RETURNS Varchar(2)
AS
BEGIN
-- Declare the return variable here
DECLARE @ReturnTime as char
-- Add the T-SQL statements to compute the return value here
DECLARE @Temp1 as dateTime
DECLARE @Temp2 as datetime
set @Temp1=0
set @Temp2=0
DECLARE @TEtoHS as int
DECLARE @TEtoMS as int
DECLARE @TEtoSS as int
DECLARE @TEtoSS11 as int
set @TEtoHS=0
set @TEtoMS=0
set @TEtoSS=0
--///////out
DECLARE @ReOutH as dateTime
DECLARE @ReOutM as dateTime
DECLARE @ReOutS as dateTime
DECLARE @ReOutTEmp as int
set @ReOutH =0
set @ReOutM=0
set @ReOutM=0
set @ReOutTEmp=0
--

set @TEtoHS=cast(substring(CONVERT(varchar, @Temp1, 114),1,2) as numeric(18))
set @TEtoMS=cast(substring(CONVERT(varchar, @Temp1, 114),4,2) as numeric(18))
set @TEtoSS=cast(substring(CONVERT(varchar, @Temp1, 114),7,2) as numeric(18))
set @TEtoHS=@TEtoHS * 3600
set @TEtoMS=@TEtoMS * 60
set @TEtoSS=@TEtoSS+ @TEtoMS+@TEtoHS
set @Temp1= @TEtoSS
--///////////////////
set @TEtoHS=cast(substring(CONVERT(varchar, @Temp2, 114),1,2)as numeric(18))
set @TEtoMS=cast(substring(CONVERT(varchar, @Temp2, 114),4,2) as numeric(18) )
set @TEtoSS=cast(substring(CONVERT(varchar, @Temp2, 114),7,2) as numeric(18))
set @TEtoHS=@TEtoHS * 3600
set @TEtoMS=@TEtoMS * 60
set @TEtoSS11=@TEtoSS11+ @TEtoMS+@TEtoHS
set @Temp2=@TEtoSS
set @TEtoSS11=@TEtoSS11-@TEtoSS-- get discord time
set @ReOutH=convert(datetime,@TEtoSS11/3600)--convert to houre
set @ReOutTEmp=@ReturnTime % 3600
set @ReOutm=convert(datetime,@ReOutTEmp/60)--convert to minute
set @ReOutTEmp=@ReOutTEmp % 60
set @ReturnTime=cast(@ReOutH as Varchar(2))+':'+CAST(@ReOutm AS Varchar(2))+':'+cast(@ReOutTEmp as Varchar(2))


-- Return the result of the function
Return cast(@ReturnTime as Varchar(2))
END