|
SQLServer时间日期函数详解,SQLServer,时间日期,
?如何实现只显示本月信息
sql="select * from [table] where datediff(m,date,Now())=0"
?
sql 查询本周本月
?
---求相差天数??
select?? datediff(day,'2004-01-01',getdate())??????
????
--1.一个月第一天的??
SELECT?? DATEADD(mm,?? DATEDIFF(mm,getdate()),?? 0)??
????
--2.本周的星期一??
SELECT?? DATEADD(wk,?? DATEDIFF(wk,?? 0)??
????
select?? dateadd(wk,datediff(wk,6)???
--3.一年的第一天??
SELECT?? DATEADD(yy,?? DATEDIFF(yy,?? 0)??
????
--4.季度的第一天??
SELECT?? DATEADD(qq,?? DATEDIFF(qq,?? 0)??
????
--5.当天的半夜??
SELECT?? DATEADD(dd,?? DATEDIFF(dd,?? 0)??
????
--6.上个月的最后一天??
SELECT?? dateadd(ms,-3,DATEADD(mm,?? 0))??
????
--7.去年的最后一天??
SELECT?? dateadd(ms,DATEADD(yy,?? 0))??
????
--8.本月的最后一天??
SELECT?? dateadd(ms,?? DATEDIFF(m,getdate())+1,?? 0))??
????
--9.本年的最后一天??
SELECT?? dateadd(ms,?? 0))??
????
--10.本月的第一个星期一??
select?? DATEADD(wk,??
DATEDIFF(wk,dateadd(dd,6-datepart(day,getdate())),?? 0)
--查询本周注册人数??
select?? count(*)?? from?? [user]??
where?? datediff(week,create_day-1,getdate())=0??
????
--上周注册人数??
select?? count(*)?? from?? [user]??
where?? datediff(week,getdate())=1??
????
????
--本月注册人数??
select?? count(*)?? from?? [user]??
where?? datediff(month,create_day,getdate())=0??
????
--上月注册人数??
select?? count(*)?? from?? [user]??
where?? datediff(month,getdate())=1??
??
--如果要效率,这样写查询??
????
--查询本周注册人数??
select?? count(*)?? from?? [user]??
where?? create_day>=dateadd(day,2-datepart(weekday,convert(varchar,getdate(),112))??
and?? create_day<dateadd(day,9-datepart(weekday,112))??
????
--上周注册人数??
select?? count(*)?? from?? [user]??
where?? create_day>=dateadd(day,-5-datepart(weekday,112))??
????
????
--本月注册人数??
select?? count(*)?? from?? [user]??
where?? create_day>=dateadd(day,1-day(getdate()),112))??
and?? create_day<dateadd(month,1,dateadd(day,112)))??
????
--上月注册人数??
select?? count(*)?? from?? [user]??
where?? create_day>=dateadd(month,-1,112)))??
and?? create_day<dateadd(day,112))??
??
--本周??
select?? count(*)?? from?? User??
where?? datediff(dd,getdate())?? <=?? datepart(dw,getdate())??
????????
--上周??
select?? count(*)?? from?? User??
where?? datediff(dd,(getdate()?? -?? datepart(dw,getdate())))?? <=?? 7??
????
--本月??
select?? count(*)?? from?? User??
where?? datepart(mm,create_day)?? =?? datepart(mm,getdate())???
--上月??
select?? count(*)?? （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
