Need Custom Start of Week

1

I have the need to pull data that is within the same week of an observation.

I've been looking at DATEADD and DATEPART, but everything I read seems to be based on the day starting at 0000.

I also intend to use DATEFIRST to ensure the day of the week is correctly defined.

Our workday begins at Sunday 1800.

How do I query the data based on the custom workday?

Thank you.

2

Don't use @@DATEFIRST -- you don't need it, it just adds unnecessary complexity.

The comparison for the week should be >= start_of_week and < end_of_week (NOT <=).


DECLARE @any_date datetime
SET @any_date = GETDATE()

SELECT DATEADD(HOUR, 18, DATEADD(DAY, -DATEDIFF(DAY, 6, @any_date) % 7,
        CAST(CAST(@any_date AS date) AS datetime))) AS start_of_week,
    DATEADD(HOUR, 18 + 24*7, DATEADD(DAY, -DATEDIFF(DAY, 6, @any_date) % 7,
        CAST(CAST(@any_date AS date) AS datetime))) AS end_of_week
3

Thank you Scott.

Will 'massage' as necessary to achieve desired results.

Enjoy your weekend.

4

I am drilling down on the structure for what you provided.

What is the % 7 doing/helping with?

I want to use the statement in WHERE criteria.

Attempting:
SELECT
[AnalysisID],[UnitNumber],[SystemName],[AnalysisName],[SampleDateTime],[AnalysisValueText]
FROM [SampleIQ_U13].[SampleIQ_App].[AnalysisValueData]
WHERE
[AnalysisID] = 468 AND
[SampleDateTime] > DATEADD(HOUR, 18, DATEADD(DAY, -DATEDIFF(DAY, 6, '6/21/2023') % 7, CAST(CAST('6/12/2023' AS date) AS datetime))) AND
[SampleDateTime] < DATEADD(HOUR, 18 + 168, DATEADD(DAY, -DATEDIFF(DAY, 6, '6/21/2023') % 7, CAST(CAST('6/21/2023' AS date) AS datetime)))

but receive no returns.