SQL Server getting value within a week time period

I need some guidance in getting value from the database within a week time period. I've figured out how to use DATEPART where I can do DATEPART(wk, date_value) = DATEPART(datepart, GETDATE()) but what I can't figure out is how to do a specific day of the week. Like I only want to retrieve information from this Tuesday to next Tuesday. Can anyone offer anyone offer any guidance?

Thanks!

In SQL Server, you can do something like this:

Set DateFirst 1;

Select DateAdd(d, -DatePart(dw, CURRENT_TIMESTAMP) + 3
        , CURRENT_TIMESTAMP) As ThisWeekTuesday
    , DateAdd(d, 7, DateAdd(d, -DatePart(dw, CURRENT_TIMESTAMP) + 3
        , CURRENT_TIMESTAMP)) As TuesdayAfterThat

In MIcrosoft Access, you would do something like:

Select DateAdd("d", -DatePart("w", Now()) + 3, Now()) As ThisWeekTuesday
    , DateAdd("d", 7, DateAdd("d", -DatePart("w", Now()) + 3
        , Now())) As TuesdayAfterThat

I recommend creating a Dates table - store one record per day, with fields for SQLDate, DateAsText, Year, Month, MonthAsText, DayOfWeek, DayOfWeekAsText, etc. Then you can query like this:

SELECT
  MIN(SQLDate)
FROM
  Dates
WHERE
      SQLDate > SYSDATETIME()
  AND DayOfWeekText = 'Tuesday'

If you'd rather not go through that right now, you can also use DATEPART() and DATEADD(). For the next coming Tuesday, I believe this would be:

DECLARE @TodaysDayOfWeek = DATEPART(DW, SYSDATETIME())
SELECT
  CASE WHEN @TodaysDayOfWeek < 3 DATEADD( 3 - @TodaysDayOfWeek, SYSDATETIME())
       ELSE                      DATEADD(10 - @TodaysDayOfWeek, SYSDATETIME() END

I don't have MS SQL handy to confirm this, but it should work if DW starts at 1 for Sunday and ends with 7 for Saturday (this can vary depending on your config). Note that, if called on a Tuesday it will give next Tuesday, not today; to reverse this behavior, change "< 3" to "<= 3".