jerome.morris (7/18/2013)
Hi Chris that returns data within them dates.Thanks
Time to put this one to bed. Can you post up the results of this batch please?
DECLARE @RangeStart DATETIME, @RangeEnd DATETIME
SELECT
@RangeStart = x1.MondayLastWeek,
@RangeEnd = DATEADD(DD,7,x1.MondayLastWeek)
FROM (
SELECT MondayLastWeek = DATEADD(hh,7,CAST(CAST(DATEADD(DD,-((DATEDIFF(DD,0,GETDATE())%7)+7),GETDATE()) AS DATE) AS DATETIME))
) x1
SELECT @RangeStart, @RangeEnd
SELECT *
FROM dbo.DocketTB
WHERE Docket_EngFinish BETWEEN @RangeStart AND @RangeEnd
SELECT *
FROM dbo.DocketTB
CROSS APPLY (
SELECT MondayLastWeek = DATEADD(hh,7,CAST(CAST(DATEADD(DD,-((DATEDIFF(DD,0,GETDATE())%7)+7),GETDATE()) AS DATE) AS DATETIME))
) x1
WHERE Docket_EngFinish BETWEEN x1.MondayLastWeek AND DATEADD(DD,7,x1.MondayLastWeek)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden