how to get last weeek dates from today to last 7 days date..

Question

i have a table with column:

Registereddate          orgid

2010-06-05 10:16:00     1
2010-06-05 10:10:00     2
2010-06-04 22:31:00     3
...                     .
.                       .....
.
.
.

i need to get only last weeek dates and orgid from today to last 7 days date..

Answer 1

Something like this:

select Registereddate, orgid
from your_table
where Registereddate > DATEADD(day, -7, GETDATE())
and DATEPART(week, Registereddate) = DATEPART(week, GETDATE())

This query will return all records having a registered date later than 7 days before current date/time.

If you don't want the hours to be taken into account, then try something like this (works only on 2008 due to date datatype cast):

select Registereddate, orgid
from your_table
where Registereddate > DATEADD(day, -7, cast(GETDATE() as date))
and DATEPART(week, Registereddate) = DATEPART(week, GETDATE())

Here's the version without the hours for 2005:

    select Registereddate, orgid
    from your_table
    where Registereddate > DATEADD(day, -7, CONVERT(datetime, CONVERT(char(10), GETDATE(), 
101)))

and DATEPART(week, Registereddate) = DATEPART(week, GETDATE())