sqldatediff

常⽤的获取时间差的sql语句

"selectcount(*)from[注册]wheredatediff(day,time,getdate())<1";//获取当天注册⼈员数

sql="selectid,classid,classname,xiaoshoue,xiaoshou,jinhuo,kucun,addtime"

sql=sql&"fromxiaoshouwhereaddtimebetween'"&format(starttime,"yyyy-mm-dd")&"'and'"

&format(endtime,"yyyy-mm-dd")&"'"

sql=sql&"orderbyiddesc"

SelectFylb,Pm,Gg,Dw,Dj,Sl,JeFROMcqyzypzxwherezxdatebetween{^2003-7-1}and

{^2003-8-1}

近两个星期

sql="select*fromtablewheredatediff(week,riqi,getdate())<=2"

近三个⽉

sql="select*fromtablewheredatediff(month,riqi,getdate())<=3"

近⼀年

sql="select*fromtablewheredatediff(year,riqi,getdate())<=1"

Apply_Datebetween#"&startdate&"23:59:59#and#"&enddate&"23:59:59#"

参考以下⽇期写法

---求相差天数

selectdatediff(day,'2004-01-01',getdate())

转贴:

--1.⼀个⽉第⼀天的

SELECTDATEADD(mm,DATEDIFF(mm,0,getdate()),0)

--2.本周的星期⼀

SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)

selectdateadd(wk,datediff(wk,0,getdate()),6)

--3.⼀年的第⼀天

SELECTDATEADD(yy,DATEDIFF(yy,0,getdate()),0)

--4.季度的第⼀天

SELECTDATEADD(qq,DATEDIFF(qq,0,getdate()),0)

--5.当天的半夜

SELECTDATEADD(dd,DATEDIFF(dd,0,getdate()),0)

--6.上个⽉的最后⼀天

SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))

--7.去年的最后⼀天

SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))

--8.本⽉的最后⼀天

SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))

--9.本年的最后⼀天

SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))

--10.本⽉的第⼀个星期⼀

selectDATEADD(wk,

DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0)

--查询本周注册⼈数

selectcount(*)from[user]

wheredatediff(week,create_day-1,getdate())=0

--上周注册⼈数

selectcount(*)from[user]

wheredatediff(week,create_day-1,getdate())=1

--本⽉注册⼈数

selectcount(*)from[user]

wheredatediff(month,create_day,getdate())=0

--上⽉注册⼈数

selectcount(*)from[user]

wheredatediff(month,create_day,getdate())=1

--如果要效率,这样写查询

--查询本周注册⼈数

selectcount(*)from[user]

wherecreate_day>=dateadd(day,2-

datepart(weekday,getdate()),convert(varchar,getdate(),112))

andcreate_day

--上周注册⼈数

selectcount(*)from[user]

wherecreate_day>=dateadd(day,-5-

datepart(weekday,getdate()),convert(varchar,getdate(),112))

andcreate_day

--本⽉注册⼈数

selectcount(*)from[user]

wherecreate_day>=dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))

andcreate_day

day(getdate()),convert(varchar,getdate(),112)))

--上⽉注册⼈数

selectcount(*)from[user]

wherecreate_day>=dateadd(month,-1,dateadd(day,1-

day(getdate()),convert(varchar,getdate(),112)))

andcreate_day