You must Sign In to post a response.
  • Category: ASP.NET

    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.


    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')
  • #706624
    Hi.,

    Try this,



    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


    Regards,
    Gandhi


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.