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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Fahhad
    New Member
    • Feb 2013
    • 4

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

    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.


    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')
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    Delete the semicolon on line 7. A semicolon ends a statement.

    Comment

    Working...