I need to pass the @startDate and @endDate as variables to @range in the code given below. But when I execute the below code, I'm getting the Error as follows. What is going wrong in my code, and how can I correct it?
Errors:
Msg 102, Level 15, State 1, Procedure GetWorkingDays2, Line 4
Incorrect syntax near ')'.
Msg 178, Level 15, State 1, Procedure GetWorkingDays2, Line 19
A RETURN statement with a return value cannot be used in this context.
use employee
go
CREATE FUNCTION dbo.GetWorkingDays2
(
@InputDate SMALLDATETIME,
);
RETURNS INT
AS
BEGIN
DECLARE @range INT,
@startDate SMALLDATETIME,
@endDate SMALLDATETIME;
SET @startDate = DATEADD(dd, -(DAY(@InputDate)-1), @InputDate);
SET @endDate = DATEADD(dd, -(DAY(DATEADD(mm, 1, @InputDate))), DATEADD(mm, 1, @InputDate));
SET @range = DATEDIFF(DAY, @startDate, @endDate)+1;
RETURN
(
SELECT
@range / 7 * 5 + @range % 7 -
(
SELECT COUNT(*)
FROM
(
SELECT 1 AS d
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
) weekdays
WHERE d <= @range % 7
AND DATENAME(WEEKDAY, @endDate - d + 1)
IN
(
'Saturday',
'Sunday'
)
) - (select count(*) from dbo.EmpTab Where EmpID = 123)
);
END
GO
--PRINT dbo.getWorkingDays2('20130228')
