本文转载:http://www.cnblogs.com/ethan-qi/archive/2013/05/14/3077371.html
其他关于SQL获取时间的方法:http://www.cnblogs.com/ethan-qi/archive/2012/04/23/2466292.html
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
网上取当月第一天和最后一天的SQL语句很多,有的是通过字符截取,有的是通过函数,个人还是比较偏向于使用内置函数来处理,
但是看了下网上的运用函数来取第一天和最后一天时间的SQL语句几乎都像下面这样的,其实是存在问题的,存在一个临界值得问题。
本月第一天:select dateadd(dd,-day(getdate())+1,getdate())
本月最后一天:select dateadd(dd,-day(getdate()),dateadd(m,1,getdate()))
上面的两句,第一句取第一天的完全没问题,第二句就有问题了,一般的想法是取最后一天,可以在当前时间上加一个月,然后减去当前时间的天数,比如随便 2009-8-27,加一个月就是2009-9-27,然后减去2009-8-27时间的天数27天 正好是2009-8-31,没问题呀。
但是,如果当前时间本身就是最后一天的话,就会产生临界问题了,比如传入的实际是2009-5-31,最终得到的最后一天的时间其实也应该是 2009-5-31才对,如果按照上面的写法,2009-5-31加一个月是多少,2009-6-31?2009-7-1?都不是,由于月大月小的问 题,6月份只有30天,所以2009-5-31加一个月后是2009-6-30日,还是按上面的写法然后再减去2009-5-31时间的天数31天,最终 得到的最后一天是2009-5-30,傻眼了,咋回事啊?
还有2月只有28或29天当然也会存在这样的问题,只要稍微改动一下,在减天数的时候不应减当前时间的天数,而应减去加了月份之后的天数,如下写法:
select dateadd(dd,-day(dateadd(m,1,getdate())),dateadd(m,1,getdate()))
这样的话,即使6月没有31天,2009-6-30减去30天就是2009-5-31,再如2009-1-30加一个月是2009-2-28,减去28天后是2009-1-31符合正确性。
可以依此类推:
可以依此类推:
select dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())) /*上个月一号*/
select dateadd(dd,-day(getdate()),getdate()) /* 上月月底 */
select dateadd(dd,-day(getdate())+1,getdate()) /* 本月一号 */
select dateadd(dd,-day(dateadd(month,1,getdate())),dateadd(month,1,getdate())) /* 本月底 */
select dateadd(dd,-day(dateadd(month,1,getdate()))+1,dateadd(month,1,getdate())) /* 下月一号 */
select dateadd(dd,-day(dateadd(month,2,getdate())),dateadd(month,2,getdate())) /* 下月月底 */
如果想把时间格式转换成“yyyy/mm/dd hh:mi:ss”这种格式,可以:
select rtrim(convert(char,getdate(),111))+''+(convert(char,getdate(),108)) -- yyyy/mm/dd hh:mi:ss
测试:(列出上月开始和结束时间,然后转换时间格式)
declare @time1 datetime,
@time2 datetime,
@time3 datetime,
@time4 datetime
set @time1 = dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())), --上个月一号
@time2 = dateadd(dd,-day(getdate())+1,getdate()) --本月一号
set @time3 = rtrim(convert(char,@time1,111))+''+(convert(char,@time1,108)),
@time4 = rtrim(convert(char,@time2,111))+''+(convert(char,@time2,108))
select dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate()))
select dateadd(dd,-day(getdate())+1,getdate())
select rtrim(convert(char,@time1,111))+''+(convert(char,@time1,108)) -- yyyy/mm/dd hh:mi:ss
select rtrim(convert(char,@time2,111))+''+(convert(char,@time2,108)) -- yyyy/mm/dd hh:mi:ss
输出结果:(sybase下)
Jan 1 2011 5:30PM
-
Feb 1 2011 5:30PM
-
2011/01/01 17:30:58
-
2011/02/01 17:30:58