Getting Error while Executing Stored Procedure on finding number of Mondays in this year till now

Question

Hi All, I want to get number of Monday/Sundays till now in this year by writing SP. We can change dayname while executing as per need. But I am getting error while executing. Can anyone please look inti the below code and made required changes?

create proc countofMondays

(
  @BeginDate date = '20230101',
  @EndDate date = '20230404',
  @DayName VARCHAR(10) = 'Monday',
  @NumberOfMondays int output
)

AS
  select @NumberOfMondays = (datediff(d, @BeginDate, @EndDate) + 1)/7 

go

declare @count int

Answer 1

Hi @Ramana Kopparapu You can use the datepart(wk,@EndDate) function, which will return the date in the week of the year. Using this function, I designed such a stored procedure, you can try it.

create or alter proc countofMondays
(
  @EndDate date,
  @DayName VARCHAR(10),
  @NumberOfMondays int output
)
AS
  if @DayName = 'Monday' 
  begin
  set datefirst 1
  select @NumberOfMondays = datepart(wk,@EndDate) - 1
  end
  if @DayName = 'Sunday'
  begin
  set datefirst 7
  select @NumberOfMondays = datepart(wk,@EndDate)
  end
go


Declare @NumberOfMondays int,
        @EndDate date = '20230404',
		@DayName VARCHAR(10) = 'Monday';
exec countofMondays @EndDate,@DayName,@NumberOfMondays output
select @NumberOfMondays;

Best regards, Percy Tang

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

But I am getting error while executing.

And which exactly error message do you get? I don't get one, but 13 as result.