ALTER FUNCTION [dbo].[udsf_WorkingHrsinMin]
(
-- Add the parameters for the function here
@StartDate Datetime, @EndDate Datetime, @Format varchar(10)
)
RETURNS VARCHAR(30)
AS
BEGIN

-- Declare the return variable here
DECLARE @Age VARCHAR(30)
DECLARE @FromDate as Datetime
DECLARE @TodayEndTime as Datetime
DECLARE @WorkingMin as INT
SET @FromDate = @StartDate

IF(Convert(Varchar(10),@StartDate,101) = Convert(Varchar(10),@EndDate,101))
BEGIN

IF(DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = 0
ELSE
SET @WorkingMin = Datediff("mi",@StartDate, @EndDate)
END

ELSE
BEGIN

WHILE(@StartDate < @EndDate)
BEGIN

IF (Convert(varchar(10),@StartDate,101) = Convert(Varchar(10),@FromDate,101))
BEGIN

IF (DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = 0
ELSE
SET @WorkingMin = Datediff("mi", @StartDate, Convert(Datetime, Convert(varchar(10),@FromDate,101) + ' 11:59 PM')) + 1
END

ELSE IF (Convert(varchar(10),@StartDate,101) = Convert(Varchar(10),@EndDate,101))
BEGIN
IF (DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = @WorkingMin + 0
ELSE
SET @WorkingMin = @WorkingMin + Datediff("mi", @StartDate, @EndDate)
END

ELSE
BEGIN
SET @TodayEndTime = Convert(Datetime, Convert(varchar(10),@StartDate,101) + ' 11:59 PM')
IF (DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = @WorkingMin + 0
ELSE
SET @WorkingMin = @WorkingMin + Datediff("mi", @StartDate, @TodayEndTime) + 1
END
SET @StartDate = Dateadd("d",1, convert(varchar(10),@StartDate,101))

END
END

IF ((@WorkingMin/1440) > 0)
BEGIN
IF (@Format = 'DDHHMM')
SELECT @Age = Convert(Varchar,(@WorkingMin/1440)) + ' D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H : ' + CONVERT(Varchar, ((@WorkingMin%1440)%60)) + ' M'
ELSE IF (@Format = 'DDHH')
SELECT @Age = Convert(Varchar,(@WorkingMin/1440)) + ' D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H'
ELSE IF (@Format = 'DD')
SELECT @Age = Convert(Varchar,(@WorkingMin/1440)) + ' D'
ELSE
SELECT @Age = @WorkingMin
END

ELSE
BEGIN
IF (@Format = 'DDHHMM')
SELECT @Age = '0 D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H : ' + CONVERT(Varchar, ((@WorkingMin%1440)%60)) + ' M'
ELSE IF (@Format = 'DDHH')
SELECT @Age = '0 D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H'
ELSE IF (@Format = 'DD')
SELECT @Age = '0 D'
ELSE
SELECT @Age = @WorkingMin
END

-- Return the result of the function
RETURN @Age

END
GO