Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 3-Mar-13 21:39pm
Comments
gvprabu at 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

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You have a Comma after the parameter. remove that and your ok... Wink | ;)
 
This code Works.. i think Wink | ;)
 
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
  Permalink  
Comments
iamFahhad at 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 at 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
0 OriginalGriff 274
1 Shweta N Mishra 216
2 PIEBALDconsult 210
3 Sergey Alexandrovich Kryukov 185
4 BillWoodruff 174
0 OriginalGriff 7,620
1 Sergey Alexandrovich Kryukov 6,937
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,495


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 4 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100