Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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')
Posted
Comments
gvprabu 4-Mar-13 3:18am    
Hi,

While getting error, Check if any syntax error is there or like any comma or single quote or single squire brackets ext...

1 solution

You have a Comma after the parameter. remove that and your ok... ;)

This code Works.. i think ;)

SQL
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
 
Share this answer
 
Comments
iamFahhad 4-Mar-13 3:57am    
Even after removing the comma, I'm getting the following errors:

Msg 102, Level 15, State 1, Procedure GetWorkingDays2, Line 11
Incorrect syntax near '@startDate'.
Msg 137, Level 15, State 1, Procedure GetWorkingDays2, Line 14
Must declare the scalar variable "@startDate".
Msg 137, Level 15, State 1, Procedure GetWorkingDays2, Line 15
Must declare the scalar variable "@endDate".
Msg 137, Level 15, State 2, Procedure GetWorkingDays2, Line 17
Must declare the scalar variable "@startDate".
Msg 137, Level 15, State 2, Procedure GetWorkingDays2, Line 36
Must declare the scalar variable "@endDate".
Msg 102, Level 15, State 1, Procedure GetWorkingDays2, Line 43
Incorrect syntax near ')'.
Paw Jershauge 4-Mar-13 3:59am    
did you copy my code ??? or edit your own ???

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900