获取时间差的sql语句

常用的获取时间差的sql语句"select count(*) from [注册] where datediff(day,time,getdate())<1";//获取当天注册人员数sql="selectid,classid,classname,xiaoshoue,xiaoshou,jinhuo,kucun,addtime "sql=sql&" from xiaoshou where addtime between '" &format(starttime,"yyyy-mm-dd") & "' and '" &format(endtime,"yyyy-mm-dd") & "'"sql=sql&" order by id desc"Select Fylb,Pm,Gg,Dw,Dj,Sl,Je FROM cqyzypzx where zxdate between {^2003-7-1}and {^2003-8-1}近两个星期sql="select * from table where datediff(week,riqi,getdate())<=2"近三个月sql="select * from table where datediff(month,riqi,getdate())<=3"近一年sql="select * from table where datediff(year,riqi,getdate())<=1" Apply_Date between #"& startdate &" 23:59:59# and #"& enddate &" 23:59:59#"参考以下日期写法---求相差天数select datediff(day,'2004-01-01',getdate())转贴:--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)--查询本周注册人数select count(*) from [user]where datediff(week,create_day-1,getdate())=0select count(*) from [user]where datediff(week,create_day-1,getdate())=1--本月注册人数select count(*) from [user]where datediff(month,create_day,getdate())=0--上月注册人数select count(*) from [user]where datediff(month,create_day,getdate())=1--如果要效率,这样写查询--查询本周注册人数select count(*) from [user]wherecreate_day>=dateadd(day,2-datepart(weekday,getdate()),convert(varch ar,getdate(),112))andcreate_day<dateadd(day,9-datepart(weekday,getdate()),convert(varchar ,getdate(),112))select count(*) from [user]wherecreate_day>=dateadd(day,-5-datepart(weekday,getdate()),convert(varc har,getdate(),112))andcreate_day<dateadd(day,2-datepart(weekday,getdate()),convert(varchar ,getdate(),112))--本月注册人数select count(*) from [user]wherecreate_day>=dateadd(day,1-day(getdate()),convert(varchar,getdate(),11 2))andcreate_day<dateadd(month,1,dateadd(day,1-day(getdate()),convert(varc har,getdate(),112)))--上月注册人数select count(*) from [user]wherecreate_day>=dateadd(month,-1,dateadd(day,1-day(getdate()),convert(v archar,getdate(),112)))andcreate_day<dateadd(day,1-day(getdate()),convert(varchar,getdate(),112) ).Net中处理两个日期相差天数的简单操作默认分类 2010-01-15 12:53:20 阅读210 评论0 字号：大中小订阅前两天在写程序的时候，为了计算两个日期相差的天数，真是大费周折啊，我才开始的时候想的是把时间格式转换为long型，后来一想，不对进制不同啊，后来我想到了数据库，用sql2005中的dateDiff函数，问题是解决了，可是每次都得和数据库交互啊！终于同事的一个大哥交了一个方法，这个方法真好，呵呵！DateTime timea=Convert.ToDateTime("2010-01-01 13:10:09");DateTime timeb = Convert.ToDateTime("2010-01-05 15:14:20");TimeSpan ts =(TimeSpan)Convert.ToDateTime(timea).Subtract(Convert.ToDateTime(timeb));ts.Days;//整天数liseconds;//整毫秒ts.Hours;//整小时ts.Minutes;//整分钟ts.Seconds;//整秒数/// <summary>/// 得到一个月的第一天/// </summary>/// <param name="someday">这个月的随便一天</param>/// <returns>DateTime</returns>public static DateTime GetFirstDayOfMonth(DateTime someday){int totalDays = DateTime.DaysInMonth(someday.Year, someday.Month); DateTime result;int ts = 1 - someday.Day;result = someday.AddDays(ts);return result;}/// <summary>/// 得到一个月的最后一天/// </summary>/// <param name="someday">这个月的随便一天</param>/// <returns>DateTime</returns>public static DateTime GetLastDayOfMonth(DateTime someday){int totalDays = DateTime.DaysInMonth(someday.Year, someday.Month); DateTime result;int ts = totalDays - someday.Day;result = someday.AddDays(ts);return result;}。