Date Ranges

I found below queries very useful when working with different date ranges.

CREATE TABLE #CHKDT

( SPNo int,

  Date1 datetime

)

INSERT INTO #CHKDT (Date1) VALUES ('6/1/2011')

INSERT INTO #CHKDT (Date1) VALUES ('6/8/2011') --

INSERT INTO #CHKDT (Date1) VALUES ('6/15/2011')

-- Find date which falls somewhere in between current week.

SELECT Date1 as 'INSIDE CURRENT WEEK' FROM #CHKDT

WHERE dateadd( week, DATEDIFF( week, 0, Date1 ), 0 ) = dateadd( week, DATEDIFF( week, 0, getdate() ), 0 )

-- Find date which is from last week

SELECT Date1 as 'DARE FROM LAST WEEK'  FROM #CHKDT

where dateadd( week, DATEDIFF( week, 0, Date1 ), 0 ) = dateadd( week, DATEDIFF( week, 0, getdate() ) - 1, 0 )

-- Find date which is in beginning of the current week

select dateadd( week, DATEDIFF( week, 0, getdate() ), 0 ) as 'Begining of current week'

-- Find date which is in beginning of last week

select dateadd( week, DATEDIFF( week, 0, getdate() ) - 1, 0 ) as 'Begining of last week'

DROP TABLE #CHKDT