By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,287 Members | 1,668 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,287 IT Pros & Developers. It's quick & easy.

Why is the "EndDate" not getting executed in my SQL Function (UDF)?

P: 4
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.


Expand|Select|Wrap|Line Numbers
  1. use employee
  2. go
  3.  
  4. CREATE FUNCTION dbo.GetWorkingDays2 
  5. @InputDate SMALLDATETIME, 
  6. ); 
  7. RETURNS INT 
  8. AS 
  9. BEGIN 
  10. DECLARE @range INT, 
  11. @startDate SMALLDATETIME,
  12. @endDate SMALLDATETIME;
  13.  
  14. SET @startDate = DATEADD(dd, -(DAY(@InputDate)-1), @InputDate);
  15. SET @endDate = DATEADD(dd, -(DAY(DATEADD(mm, 1, @InputDate))), DATEADD(mm, 1, @InputDate));
  16. SET @range = DATEDIFF(DAY, @startDate, @endDate)+1; 
  17.  
  18. RETURN 
  19. SELECT 
  20. @range / 7 * 5 + @range % 7 - 
  21. SELECT COUNT(*) 
  22. FROM 
  23. SELECT 1 AS d 
  24. UNION ALL SELECT 2 
  25. UNION ALL SELECT 3 
  26. UNION ALL SELECT 4 
  27. UNION ALL SELECT 5 
  28. UNION ALL SELECT 6 
  29. UNION ALL SELECT 7 
  30. ) weekdays 
  31. WHERE d <= @range % 7 
  32. AND DATENAME(WEEKDAY, @endDate - d + 1) 
  33. IN 
  34. 'Saturday', 
  35. 'Sunday' 
  36. ) - (select count(*) from dbo.EmpTab Where EmpID = 123)
  37. ); 
  38. END 
  39. GO



Expand|Select|Wrap|Line Numbers
  1. --PRINT dbo.getWorkingDays2('20130228')
Mar 4 '13 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,430
Delete the semicolon on line 7. A semicolon ends a statement.
Mar 4 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.