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

haiwei2009

http://hi.baidu.com/zjyyo/blog/item/8b60c97f316f7f0e28388a9a.html

//针对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