How do I get the last date of a weekday in a given month in SQL Server?

8

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

    
asked by anonymous 13.12.2013 / 17:45

3 answers

6

I have the following function that returns the last day of the 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 usage and returned values, using the example of the question:

-- 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 at sqlfiddle .

Note: Stay tuned for the DATEFIRST property because it can change the results.     

13.12.2013 / 18:51
3

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
    
13.12.2013 / 18:57
-1

I solved this problem with the following solution, I hope I 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
    
03.08.2015 / 15:52