选择日期字段在本周内具有值的行

从星期天到星期六这一周有效。如果星期一到星期日需要几周时间,则应进行调整:

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
         ;

我是用微软的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

星期日是一周的开始。