t-sql help. need to return that current week of data for date parameter

Question

I have a stored procedure that needs to return a weekly schedule of items being made on different days. It takes in a start date.

@StartDate DATETIME

Based on the startdate passed in, I need to return all the rows of data for that week, with the week starting on Tuesday and ending the following Monday.

Only taking in a startdate, how do I set the start of the week to Tuesday and then have my query return data from that particular week's range?

I know NOTHING of date functions in SQL.

I did see you can set DATEFIRST as 2 (which I think is Tuesday), but I don't know how to take in the date and find that week. I assume I need two variables to set the beginning of the week date and the end of the week date and return data in that range?

Here's my query that currently only returns the current date passed in:

SELECT 
    PS.ProductionDate,
    L.Name,
    S.Name,
    PS.PartNo,
    --PM.DisplayName,
    PS.LotNo,
    WC.Name,
    WC2.Name,
    PSS.Name,
    PS.Mixing,
    PS.Glazing,
    PS.StartTime,
    PS.[Weight],
    PS.Pallets
    FROM PROD_ProductionSchedule PS
    --LEFT OUTER JOIN PartMaster PM on PS.PartNo = PM.Name
    LEFT OUTER JOIN Location L on PS.LocationId = L.Id
    LEFT OUTER JOIN PROD_Shifts S on PS.ShiftId = S.Id
    LEFT OUTER JOIN PROD_WorkCenters WC on PS.OvenId = WC.Id
    LEFT OUTER JOIN PROD_WorkCenters WC2 on PS.LineId = WC2.Id
    LEFT OUTER JOIN PROD_ProductionScheduleStatus PSS ON PS.ScheduleStatusId = PSS.Id
    WHERE PS.ProductionDate=@StartDate 
    AND PS.LocationId = CASE WHEN @Location = -1 THEN PS.LocationId ELSE @Location END
    AND PS.PartNo = CASE WHEN @PartNo = 'ALL' THEN PS.PartNo ELSE @PartNo END 

Answer 1

Get the Tuesday of the current week and 7 days later:

select dateadd(dd, 3 - datepart(dw, getdate()), 
getdate()), dateadd(dd, 10 - datepart(dw, getdate()), getdate())

Answer 2

Calculate the beginning and end of the week:

declare @StartOfWeek datetime
declare @EndOfWeek datetime

if DATEPART(weekday,@StartDate) = 3 --Tuesday
begin
    set @StartOfWeek = @StartDate
    set @EndOfWeek = dateadd(week,1,@StartDate)
end
else if DATEPART(weekday,@startdate) > 3
begin
    set @StartOfWeek = dateadd(DAY,-(DATEPART(weekday,@startdate)-3),@StartDate)
    set @EndOfWeek = dateadd(DAY,10-DATEPART(weekday,@startdate),@StartDate)
end
else
begin
    set @StartOfWeek = dateadd(DAY,-4-(DATEPART(weekday,@startdate)),@StartDate)
    set @EndOfWeek = dateadd(DAY,3-DATEPART(weekday,@startdate),@StartDate)
end

Then you can use these in your Where clause:

WHERE PS.ProductionDate between @StartOfWeek and @EndOfWeek

Answer 3

First, create a calendar table with two columns that contain the first and last days of the working week for any given date. Then you can write some very clear, simple code:

declare @FirstDayOfWorkingWeek date,
        @LastDayOfWorkingWeek date

select 
    @FirstDayOfWorkingWeek = FirstDayOfWorkingWeek, 
    @LastDayOfWorkingWeek = LastDayOfWorkingWeek
from
    dbo.Calendar
where
    [Date] = @StartDate
    
select *
from dbo.PROD_ProductionSchedule
where ProductionDate between @FirstDayOfWorkingWeek and @LastDayOfWorkingWeek

And depending on how you store your dates, be aware of the potential issues with using BETWEEN.

Using a calendar table is almost always preferable to using functions for date calculations because you can read and verify the dates easily; you can UPDATE your table to handle exceptions to the regular logic; your code is usually much more readable and easier to maintain; and performance can be better if you JOIN on a table rather than use nested functions.