CODEWITHSUNDEEP
sqldatetimedatefirebird
Ed.C
Ed.C

Reputation: 798

Select rows with date field having values within this week

I am trying to select all the rows from a table where a date field has values within the current week. I want the rows from the current week's Monday until the current day.

Example: 

ID   adate
---------------
1    11-11-2010
2    12-11-2010
3    13-11-2010
4    14-11-2010
5    15-11-2010

The rows I want in this case are:

ID   adate
---------------
4    14-11-2010  //this week's Monday
5    15-11-2010  //till today

Upvotes: 1

Views: 7050

Answers (2)

Eric K Yung
Eric K Yung

Reputation: 1784

I wrote it in ms sql:

declare @today as datetime
declare @first_day_of_week datetime
set @today = convert(varchar, getDate(), 101)
set @first_day_of_week = dateadd(day, -(DATEPART(WEEKDAY, @today) - 1), @today)
select *
from [table]
where adate between @first_day_of_week and @today

Sunday is the beginning of the week.

Upvotes: 1

jachguate
jachguate

Reputation: 17203

This will work on a week from Sunday to Saturday. You shall adapt it if you want weeks from Monday to Sunday:

select *
  from myTable
 where aDate between
         cast('now' as date) - extract(weekday from cast('now' as date))  --prev sunday
         and
         cast('now' as date) - extract(weekday from cast('now' as date)) + 6  --next saturday
         ;

Upvotes: 2

Related Questions