ASP.NET按时间查询

select  top 6 *  from ViewHouseSale  where  1=1 and  (EstateType='0' or EstateType='1')
and DateDiff(day,PubDate,getdate()) <=7 and IfBenefit='Y' and IfValid='Y'  order by PubDate DESC
select  top 6 *  from ViewHouseSale  where  1=1 and  (EstateType='0' or EstateType='1')
PubDate  between  23:59:59' and  23:59:59'   and IfBenefit='Y' and IfValid='Y'  order by PubDate DESC
--当天
(usrRegTime >=CONVERT(varchar(10),getDate(),120)+' 00:00:00' and usrRegTime <=CONVERT(varchar (10),getDate(),120)+' 23:59:59')
--近三天
DateDiff(day,usrRegTime,getdate()) <=3
--本周
Datepart(year,usrRegTime)=DatePart(year,Getdate()) and DatePart(week,usrRegTime)=DatePart (week,GetDate())
--本月
Datepart(year,usrRegTime)=DatePart(year,Getdate()) and DatePart(month,usrRegTime)=DatePart (month,GetDate())