mHelpMe mHelpMe - 7 months ago 16
SQL Question

create a view to have relative dates

I have a query which returns data between two dates. Typically those dates are Monday the previous week & Friday the previous week. I've been asked to make this query a view. I have instead made it a stored procedure where the user can enter the two dates. However I would like to know if it would be possible to create a view purely out of my own interest.

Eg. Today is Thursday 21st April. So the from date would be Monday 11th April and the to date would be Friday 15th April.

Is there anyway to setup my query so no matter what day it is this week it will select Monday & Fridays date? Then obviously next week it would automatically select the from date as 18th & the to date as 22nd?

Answer

To find Monday and Friday of last week (this is not sensitive to DATEFIRST):

CAST(DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE())-1,0) AS DATE) 'Monday Last Week'
CAST(DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE())-1,4) AS DATE) 'Friday Last Week'

This works because Date = 0 was a Monday:

SELECT DATENAME(WEEKDAY, 0) 'What Was Day Zero'

What Was Day Zero
------------------------------
Monday

So your view would be something like (untested):

CREATE VIEW SomeView AS
  SELECT 
    SomeCols 
  FROM 
    SomeTable S
  WHERE  S.SomeDate BETWEEN
    CAST(DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE())-1,0) AS DATE)
    AND 
    CAST(DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE())-1,4) AS DATE);