Pick start and end date of one week of the month

Question

Good afternoon, you guys.

Today I’m having a problem getting the start date and end of a week of the month. Let’s go by parts:

I have a combo that brings me the weeks of a month posted. To know how many weeks I have in January 2015, for example, I use the following SQL:

SELECT theLast - theFirst + 1 AS Semanas
FROM (
    SELECT 1 + Number AS theMonth,
    DATEDIFF(DAY, -1, DATEADD(MONTH, Number, DATEADD(YEAR, 2015 - 1900, 0))) / 7 AS theFirst,
    DATEDIFF(DAY, -1, DATEADD(MONTH, Number, DATEADD(YEAR, 2015 - 1900, 30))) / 7 AS theLast
    FROM master..spt_values
    WHERE Type = 'P' AND Number < 12
) AS d 
WHERE d.theMonth = 01

Up to this point ok, very quiet. Returned 5 weeks. The problem is in finding the beginning and the end of a week. For example, if I post week = 03 and month = 01, you should show me '2015-01-11' as initial and '2015-01-17' as final.

Does anyone have any idea how to do that?

Answer 1

In the forum of Microsoft, gave me a solution:

declare @MêsAno varchar(7), @Dia1 DATE, @Id INT = 1;

set @MêsAno = '1/2015';  --< mês/ano, sem espaços

set @Dia1= Convert(date, '1/' + @MêsAno, 103);

set DateFirst 7;

declare @InicioSemana date, @FinalSemana date;

set @InicioSemana= DateAdd(day, 1 - DatePart(weekday, @Dia1), @Dia1);

set @FinalSemana= DateAdd(day, +6, @InicioSemana);

while Month(@InicioSemana) = Month(@Dia1) or Month(@FinalSemana) =
Month(@Dia1) 
  begin
    SELECT @Id, @InicioSemana, @FinalSemana;
    set @InicioSemana= DateAdd(day, +7, @InicioSemana);
    set @FinalSemana= DateAdd(day, +6, @InicioSemana);
    set @Id = @Id+1;
  END;

Answer 2

The following command takes the first Monday of the month:

SELECT @PrimeiraSegundaFeira = DATEADD(WEEK, DATEDIFF(WEEK, 0,
    DATEADD(DAY, 6 – DATEPART(DAY, GETDATE()), GETDATE())), 0)

The Monday of the third week can thus be obtained:

SELECT @TerceiraSegundaFeira = DATEADD(WEEK, 2, @PrimeiraSegundaFeira)

The Friday of the third week can thus be obtained:

SELECT @TerceiraSextaFeira = DATEADD(DAY, 5, @TerceiraSegundaFeira)