Limit Single Select field to Certain Days of the Week (not just Sunday)

Question

Client: Emmanuel

We are currently using the code below to limit a single-select date field to only the current + past 6 Sunday dates. Can this be done for other days of the week? We need to include Sunday AND Wednesday dates for the current week + the past 6 weeks.

DECLARE @SundayDate DateTime = [dbo].[ufnUtility_GetSundayDate](GetDate())
DECLARE @NextDate DateTime
DECLARE @WeeksBack int = 6
DECLARE @LoopInt int = 1

DECLARE @TmpTable TABLE (
        [Value] DATETIME NULL,
        [Text] VARCHAR(25) NULL
)

INSERT INTO @TmpTable VALUES( @SundayDate , CONVERT(varchar, @SundayDate, 1))

WHILE @LoopInt <= @WeeksBack
BEGIN
    SET @NextDate = DATEADD(WEEK, (SELECT @LoopInt * -1), @SundayDate)
    INSERT INTO @TmpTable VALUES( @NextDate , CONVERT(varchar, @NextDate, 1))

    SET @LoopInt = @LoopInt + 1
END

SELECT * FROM @TmpTable

Answer 1

Here is a solution using the AnalyticsSourceDate table.

SELECT 
    Format([Date], 'yyyy-MM-dd 00:00:00.000') as [Value],
    Format([Date],'M/d/yy') as [Text]
FROM 
    AnalyticsSourceDate
WHERE
    [Date] >  DATEADD(WEEK, -6, GETDATE())
    and [Date] <=DATEADD(DAY, 6, GETDATE())
    and [DayOfWeek] in (0,3) --Sunday and Wednesday
ORDER BY [Date] DESC

Comments

Thank you, @MarkLee !