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

Ramana Kopparapu 226 Reputation points
2023-04-04T04:46:43.1+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,860 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-04-04T06:23:28.9066667+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 41,011 Reputation points
    2023-04-04T06:37:27.4833333+00:00

    But I am getting error while executing.

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