SQL中关于时间字段（日期字段）查询的总结

//针对MS -SQL数据库，一些常用的时间查询语句,以备不时之需：
表名：news || 时间字段名称：newsDatetime || getdate() 为获得系统时间
1.查询 2000-1-1 and 2008-5-1 期间的所有记录，降序
select * from news where newsDatetime between '2000-1-1' and '2008-5-1' order by newsDateTime desc
­2.查询本天、本月、本年所有记录
select * from news where datediff(day,newsDatetime,getdate())=0
select * from news where datediff(month,newsDatetime,getdate())=0
select * from news where datediff(year,newsDatetime,getdate())=0 ­
3.查询本月所有记录的另外一种方法
select * from news where datepart(mm, newsDatetime) = datepart(mm, getdate()) and datepart(yy,newsDatetime) = datepart(yy, getdate()) ­
4.查询本季度所有记录
select * from news where datepart(qq, newsDatetime) = datepart(qq, getdate()) and datepart(yy,newsDatetime) = datepart(yy, getdate()) ­
5.查询本周所有记录
select * from news where newsDatetime between getdate() and datediff(day,-7,getdate())
­6.查询本周所有记录的另外一种方法
select * from news where datepart(wk, newsDatetime) = datepart(wk, getdate()) and datepart(yy,newsDatetime) = datepart(yy, getdate()) ­
7.查询昨天、上月、去年所有记录
select * from news where datediff(day,newsDatetime,getdate())=1
select * from news where datediff(month,newsDatetime,getdate())=1
select * from news where datediff(year,newsDatetime,getdate())=1 ­
8.查询某年（此为2004年）所有记录
select * from news where year(newsDatetime)=2004 ­
9.查询某年（此为2004年）、某月（此为3月）所有记录
select * from news where year(newsDatetime)=2008 and month(newsDatetime)=3