How to get the last date of a day of the week in a given month in SQL Server?

Question

Using the T-SQL language of SQL Server, how to get, for example, the latest wednesday of the month of May of a given year?

Answer 1

I assembled the following function which returns the last day of week within a month:

CREATE FUNCTION dbo.P_LAST_WEEK_DAY_OF_MONTH(
  @YEAR INT, 
  @MONTH INT,
  @WEEK_DAY INT) RETURNS DATETIME 
AS
BEGIN
DECLARE @DATE_AUX DATETIME

-- fisrt day of month
SET @DATE_AUX = CONVERT(DATETIME, CONVERT(VARCHAR, @YEAR) + '-' + CONVERT(VARCHAR, @MONTH) + '-01')

-- last day of month
SET @DATE_AUX = DATEADD(D, -1, DATEADD(M, 1, @DATE_AUX))

-- last weekday of month
RETURN DATEADD(D, 
               @WEEK_DAY
               - DATEPART(DW, @DATE_AUX)
               - (CASE WHEN DATEPART(DW, @DATE_AUX) < @WEEK_DAY THEN 7 ELSE 0 END),
               @DATE_AUX)

END
GO

The following excerpt shows the use and values returned, using the question example:

-- 1 -> Sunday, 2-> Monday, 3 -> Tuesday, ...

SELECT dbo.P_LAST_WEEK_DAY_OF_MONTH(2013, 4, 1), -- returns 28
       dbo.P_LAST_WEEK_DAY_OF_MONTH(2013, 4, 2), -- returns 29
       dbo.P_LAST_WEEK_DAY_OF_MONTH(2013, 4, 3), -- returns 30
       dbo.P_LAST_WEEK_DAY_OF_MONTH(2013, 4, 4), -- returns 24
       dbo.P_LAST_WEEK_DAY_OF_MONTH(2013, 4, 5), -- returns 25
       dbo.P_LAST_WEEK_DAY_OF_MONTH(2013, 4, 6), -- returns 26
       dbo.P_LAST_WEEK_DAY_OF_MONTH(2013, 4, 7)  -- returns 27  

I checked the results using this calendar in the sqlfiddle.

Note: stay tuned with the property DATEFIRST, because it can alter the results.

Answer 2

I did a slightly different implementation of @utluiz:

CREATE FUNCTION [dbo].[UltimoDiaSemana](@Data AS DATE, @DiaSemana AS INT)
RETURNS DATE
AS
BEGIN
    DECLARE @UltimoDiaMes DATETIME
    DECLARE @DW INT
    SET @UltimoDiaMes = DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, @Data) + 1, 0))
    SET @DW = DATEPART(DW, @UltimoDiaMes)
    IF @DW < @DiaSemana
  BEGIN
        SET @DW = @DW + 7
    END
    RETURN DATEADD(d, @DiaSemana - @DW, @UltimoDiaMes)
END

Being Wednesday = 4:

SELECT dbo.UltimoDiaSemana('2013-05-01', 4) -- 2013-05-29
SELECT dbo.UltimoDiaSemana('2012-05-01', 4) -- 2012-05-30
SELECT dbo.UltimoDiaSemana('2011-05-01', 4) -- 2011-05-25

Answer 3

I solved this problem with the following solution, I hope to have contributed

DECLARE @DT DATETIME = '2015-12-10'
DECLARE @DT_INICIO_SEMANA DATETIME 
DECLARE @DT_FIM_SEMANA DATETIME 
DECLARE @DT_INICIO_MES DATETIME 
DECLARE @DT_FIM_MES DATETIME 

SET @DT_INICIO_SEMANA = DATEADD(DAY,-1 * (DATEPART(DW,@DT)-1), @DT)
SET @DT_FIM_SEMANA = DATEADD(DAY,6, @DT_INICIO_SEMANA)

SET @DT_INICIO_MES = DATEADD(DAY,-1 * (day(@DT) -1), @DT)
SET @DT_FIM_MES = DATEADD(day,-1, DATEADD(month,1, @DT_INICIO_MES))

SELECT @DT_INICIO_SEMANA DT_INICIO_SEMANA, @DT_FIM_SEMANA DT_FIM_SEMANA,@DT_INICIO_MES DT_INICIO_MES, @DT_FIM_MES DT_FIM_MES