update:
ada tambahan di functions,scalar valueable,
nama function nya fn_diffTime
isi query sebagai berikut :
USE [MinovaES_Rohto_Prod]
GO
/****** Object: UserDefinedFunction [dbo].[fn_diffTime] Script Date: 08/03/2022 15:39:05 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_diffTime]
(
-- Add the parameters for the function here
@firstDateStr varchar(8),
@secondDateStr varchar(8),
@firstTime AS varchar(8),
@secondTime AS varchar(8)
)
RETURNS decimal(18,2)
AS
BEGIN
--DECLARE @firstDateStr AS varchar(8) = '20141001'
--DECLARE @secondDateStr AS varchar(8) = '20141001'
--DECLARE @firstTime AS varchar(8) = '1830'
--DECLARE @secondTime AS varchar(8) = ''
--DECLARE @type AS nvarchar(50) = hour
-- Declare the return variable here
DECLARE @firstDate AS datetime
DECLARE @secondDate AS datetime
DECLARE @diffDate AS decimal(18,2)
if (@secondTime = '')
SET @secondTime =
(select @firstTime)
if (@firstDate = '')
SET @firstDate =
(select @secondTime)
SET @firstDate = CAST((SUBSTRING(@firstDateStr, 1, 4) + '-' + SUBSTRING(@firstDateStr, 5, 2) + '-' + SUBSTRING(@firstDateStr, 7, 2) +
' ' + SUBSTRING(@firstTime, 1, 2) + ':' + SUBSTRING(@firstTime, 3, 2)) AS datetime)
SET @secondDate = CAST((SUBSTRING(@secondDateStr, 1, 4) + '-' + SUBSTRING(@secondDateStr, 5, 2) + '-' + SUBSTRING(@secondDateStr, 7, 2) +
' ' + SUBSTRING(@secondTime, 1, 2) + ':' + SUBSTRING(@secondTime, 3, 2)) AS datetime)
-- Add the T-SQL statements to compute the return value here
SELECT @diffDate = Datediff(minute, @firstDate, @secondDate)
if (@diffDate < 0)
begin
SET @diffDate = 0
end
else
begin
--SET @secondDate =
--(select dateadd(dd,1,@secondDate))
SET @diffDate =
(select Datediff(minute, @firstDate, @secondDate))
-- Return the result of the function
SET @diffDate =
(select @diffDate/60)
end
--SET @diffDate =
--(select round(@diffDate,2))
RETURN @diffDate
END
