Using the SQL Server T-SQL language, how to get, for example, the last Wednesday of the month of May in a given year?
Using the SQL Server T-SQL language, how to get, for example, the last Wednesday of the month of May in a given year?
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.
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
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