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

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

Author: ricidleiv, 2013-12-13
Source

3 answers

I set up the following function that returns the last weekday 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 usage and the returned values, 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 check the results using this calendar in sqlfiddle.

Note: be aware of the DATEFIRST property, as it can change the results.

 6
Author: utluiz, 2013-12-17 14:00:53

I made 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
 3
Author: augustomen, 2013-12-25 23:23:04

I solved this problem with the following solution, I hope I 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
 -1
Author: Alexandro Silveira, 2015-08-03 14:03:20