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.
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.
Code:
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
Code:
--PRINT dbo.getWorkingDays2('20130228')
Comment