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
- 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
Expand|Select|Wrap|Line Numbers
- --PRINT dbo.getWorkingDays2('20130228')