select * from tbl where DATEPART(wk,date_field) = DATEPART(wk,getDate()) And DATEPART(yyyy,date_field) = DATEPART(yyyy,getDate())--周
select * from tbl where DATEPART(m,date_field) = DATEPART(m,getDate()) And DATEPART(yyyy,date_field) = DATEPART(yyyy,getDate())--月
select * from tbl where DATEPART(q,date_field) = DATEPART(q,getDate()) And DATEPART(yyyy,date_field) = DATEPART(yyyy,getDate()) --季
select * from tbl where DATEPART(yyyy,date_field) = DATEPART(yyyy,getDate()) --年
Access wk之类的加引号 "wk"
getDATE()是什么东东,可不可以用date()来代替?
在SQL中不能用date()代替
它在那相当于now()
举例一周的
select * from 表 where datediff(week,时间字段,getdate())=0
要注意的是,datediff是将星期日做为一周的第一天
即:datediff(week,'2003-07-27','2003-08-01')的值为0
如果你要求将星期一做为一周的第一天,就要用:
select * from 表 where datediff(week,时间字段+1,getdate()+1)=0
//=====================================================================//
1.一个月第一天的
Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
2.本周一
Select DATEADD(wk, DATEDIFF(wk,0,getdate()),0)
Select dateadd(wk,datediff(wk,0,getdate()),6)
3.一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
4.季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
5.当天的半夜
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
6.上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
7.去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
8.本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
9. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
10.本月的第一个星期一
select DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
11.查询本周注册人数
select count(*) from [user] where datediff(week,create_day-1,getdate())=0
12.上周注册人数
select count(*) from [user] where datediff(week,create_day-1,getdate())=1
13.本月注册人数
select count(*) from [user] where datediff(month,create_day,getdate())=0
14.上月注册人数
select count(*) from [user]
where datediff(month,create_day,getdate())=1
如果要效率,这样写查询
1.查询本周注册人数
select count(*) from [user]
where create_day>=dateadd(day,2-datepart(weekday,getdate()),convert(varchar,getdate(),112))
and create_day<dateadd(day,9-datepart(weekday,getdate()),convert(varchar,getdate(),112))
2.上周注册人数
select count(*) from [user]
where create_day>=dateadd(day,-5-datepart(weekday,getdate()),convert(varchar,getdate(),112))
and create_day<dateadd(day,2-datepart(weekday,getdate()),convert(varchar,getdate(),112))
3.本月注册人数
select count(*) from [user]
where create_day>=dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))
and create_day<dateadd(month,1,dateadd(day,1-day(getdate()),convert(varchar,getdate(),112)))
4.上月注册人数
select count(*) from [user]
where create_day>=dateadd(month,-1,dateadd(day,1-day(getdate()),convert(varchar,getdate(),112)))
and create_day<dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))
5.本周
select count(*) from User
where datediff(dd,create_day,getdate()) <= datepart(dw,getdate())
6.上周
select count(*) from User
where datediff(dd,create_day,(getdate() - datepart(dw,getdate()))) <= 7
7.本月
select count(*) from User
where datepart(mm,create_day) = datepart(mm,getdate())
8.上月
select count(*) from User
where datepart(mm,create_day) = datepart(mm,getdate()) - 1
9.本周
select count(*) from [User]
where datediff(dd,create_day,getdate()) <= datepart(dw,getdate())
10.上周
select count(*) from [User]
where datediff(dd,create_day,(getdate() - datepart(dw,getdate()))) <= 7
11.本月
select count(*) from [User]
where datepart(mm,create_day) = datepart(mm,getdate())
12. 上月
select count(*) from [User]
where datepart(mm,create_day) = datepart(mm,getdate()) - 1
本贴转自：http://hi.baidu.com/%D7%B7%C3%CE2009/blog/item/d27a6e12d36b052edc54017d.html
posted on
