CREATE FUNCTION [dbo].[fnDaysInMonthExcludingWeekends]
(@Year INT,
@Month INT)
RETURNS INT
AS
BEGIN
DECLARE @FirstDay SMALLDATETIME,
@LastDay SMALLDATETIME,
@Days INT
DECLARE @RunningDay SMALLDATETIME
SET @FirstDay = convert(SMALLDATETIME,convert(VARCHAR,@Year) + '/'
+ convert(VARCHAR,@Month) + '/01')
SET @LastDay = dateadd(DAY,-1,dateadd(MONTH,1,@FirstDay))
SET @Days = 0
SET @RunningDay = @FirstDay
WHILE (@RunningDay <= @LastDay)
BEGIN
IF datepart(dw,@RunningDay) not in (1, 7) -- Exclude SUN, SAT
SET @Days = @Days + 1
SET @RunningDay = @RunningDay + 1
END
RETURN (@Days)
END
You need to create table valued function for it, instead of sacalar function.
--SELECT * FROM dbo.[fnDaysInMonthExcludingWeekends]( 2013, 1, 6)
ALTER FUNCTION [dbo].[fnDaysInMonthExcludingWeekends](@Year INT, @Month INT, @CTR INT)
RETURNS @tab TABLE (days INT)
AS
BEGIN
DECLARE @FirstDay SMALLDATETIME,
@LastDay SMALLDATETIME,
@Days INT,
@RunningDay SMALLDATETIME,
@CNT INT
SET @CNT = 0
WHILE @CNT < @CTR
BEGIN
SET @FirstDay = dateadd(M, @CNT, convert(SMALLDATETIME,convert(VARCHAR,@Year) + '/' + convert(VARCHAR,@Month) + '/01'))
SET @LastDay = dateadd(DAY,-1,dateadd(MONTH,1,@FirstDay))
SET @Days = 0
SET @RunningDay = @FirstDay
WHILE (@RunningDay <= @LastDay)
BEGIN
IF datepart(dw,@RunningDay) not in (1, 7) -- Exclude SUN, SAT
SET @Days = @Days + 1
SET @RunningDay = @RunningDay + 1
END
INSERT INTO @tab (days) VALUES (@Days)
SET @CNT = @CNT + 1
END
RETURN
END
NOTE : you need to first drop your existing function and then create, because alter of function from sacalar function to table valued function is not allowed
Execution of function is not allowed as a parameter. First create variable and set values and then pass to function.
Alternatively, In your case, without year and month as parameter, you can do the same in the function itself
--SELECT * FROM dbo.[fnDaysInMonthExcludingWeekends](6)
ALTER FUNCTION [dbo].[fnDaysInMonthExcludingWeekends](@CTR INT)
RETURNS @tab TABLE (days INT)
AS
BEGIN
DECLARE @FirstDay SMALLDATETIME,
@LastDay SMALLDATETIME,
@Days INT,
@RunningDay SMALLDATETIME,
@CNT INT,
@Year INT,
@Month INT
SET @Year = YEAR(getdate())
SET @Month = MONTH(getdate())
SET @CNT = 0
WHILE @CNT < @CTR
BEGIN
SET @FirstDay = dateadd(M, @CNT, convert(SMALLDATETIME,convert(VARCHAR,@Year) + '/' + convert(VARCHAR,@Month) + '/01'))
SET @LastDay = dateadd(DAY,-1,dateadd(MONTH,1,@FirstDay))
SET @Days = 0
SET @RunningDay = @FirstDay
WHILE (@RunningDay <= @LastDay)
BEGIN
IF datepart(dw,@RunningDay) not in (1, 7) -- Exclude SUN, SAT
SET @Days = @Days + 1
SET @RunningDay = @RunningDay + 1
END
INSERT INTO @tab (days) VALUES (@Days)
SET @CNT = @CNT + 1
END
RETURN
END
anilr499
Member
94 Points
389 Posts
how to give input to function in sql to take input from todays date using sql
Jan 07, 2013 12:49 AM|LINK
Hi
i have a function in sql
which need 2 inputs year and month....
now how can i give input dynamically year and month (number) from
system date....
i need this month and next 6 months using sql...
my query is
SELECT dbo.fnDaysInMonthExcludingWeekends(2013,1) as col
output:
col
23
Thank you
Kulrom
Contributor
4834 Points
893 Posts
Re: how to give input to function in sql to take input from todays date using sql
Jan 07, 2013 12:56 AM|LINK
My Blog: ASP.NET Stuff
anilr499
Member
94 Points
389 Posts
Re: how to give input to function in sql to take input from todays date using sql
Jan 07, 2013 01:03 AM|LINK
thank you for your support...
that is fine ....
but i need the result for next six months....
now when i execute this
SELECT dbo.fnDaysInMonthExcludingWeekends(YEAR(GETDATE()),MONTH(GETDATE())) as col
result is
col
23
but my required result is should be for 6mnths non working days
col
23
20
21
22
23
20
sandeepmitta...
Contributor
6803 Points
1060 Posts
Re: how to give input to function in sql to take input from todays date using sql
Jan 07, 2013 02:15 AM|LINK
Share definition of the function
Sandeep Mittal | My Blog - IT Developer Zone
anilr499
Member
94 Points
389 Posts
Re: how to give input to function in sql to take input from todays date using sql
Jan 07, 2013 04:55 AM|LINK
hi
here is my function
CREATE FUNCTION [dbo].[fnDaysInMonthExcludingWeekends]
(@Year INT,
@Month INT)
RETURNS INT
AS
BEGIN
DECLARE @FirstDay SMALLDATETIME,
@LastDay SMALLDATETIME,
@Days INT
DECLARE @RunningDay SMALLDATETIME
SET @FirstDay = convert(SMALLDATETIME,convert(VARCHAR,@Year) + '/'
+ convert(VARCHAR,@Month) + '/01')
SET @LastDay = dateadd(DAY,-1,dateadd(MONTH,1,@FirstDay))
SET @Days = 0
SET @RunningDay = @FirstDay
WHILE (@RunningDay <= @LastDay)
BEGIN
IF datepart(dw,@RunningDay) not in (1, 7) -- Exclude SUN, SAT
SET @Days = @Days + 1
SET @RunningDay = @RunningDay + 1
END
RETURN (@Days)
END
sandeepmitta...
Contributor
6803 Points
1060 Posts
Re: how to give input to function in sql to take input from todays date using sql
Jan 07, 2013 06:46 AM|LINK
You need to create table valued function for it, instead of sacalar function.
NOTE : you need to first drop your existing function and then create, because alter of function from sacalar function to table valued function is not allowed
Sandeep Mittal | My Blog - IT Developer Zone
anilr499
Member
94 Points
389 Posts
Re: how to give input to function in sql to take input from todays date using sql
Jan 07, 2013 07:12 AM|LINK
thankyou .....it worked for me.....
but when i tried like this....
SELECT * FROM dbo.[fnDaysInMonthExcludingWeekends](YEAR(getdate), MONTH(GETDATE), 6)
am getting error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
sandeepmitta...
Contributor
6803 Points
1060 Posts
Re: how to give input to function in sql to take input from todays date using sql
Jan 07, 2013 07:53 AM|LINK
Execution of function is not allowed as a parameter. First create variable and set values and then pass to function.
Alternatively, In your case, without year and month as parameter, you can do the same in the function itself
Sandeep Mittal | My Blog - IT Developer Zone
anilr499
Member
94 Points
389 Posts
Re: how to give input to function in sql to take input from todays date using sql
Jan 07, 2013 08:41 AM|LINK
thank you very much
Mr.sandeepmittal11
....
i learnt a lot from this.....
sandeepmitta...
Contributor
6803 Points
1060 Posts
Re: how to give input to function in sql to take input from todays date using sql
Jan 07, 2013 03:39 PM|LINK
You are most welcome
You should always mark as answer to the posts that helped you, so that it can help others also viewing the posts.
Sandeep Mittal | My Blog - IT Developer Zone