在做Sql Server开发的时候有时需要获取表中今天、昨天、本周、上周、本月、上月等数据,这时候就需要使用DATEDIFF()函数及GetDate()函数了。
DATEDIFF ( datepart , startdate , enddate )
释义:计算时间差
datepart值:year | quarter | month | week | day | hour | minute | second | millisecond
startdate:开始日期
enddate :结束日期
GetDate() :获取当前的系统日期
下面例子中表名为Date,条件字段名为dateday
查询今天
SELECT * FROM Date where DATEDIFF(day,dateday,GETDATE())=0
查询昨天
SELECT * FROM Date where DATEDIFF(day,dateday,GETDATE())=1
查询本周
SELECT * FROM Date where DATEDIFF(week,dateday,GETDATE())=0
查询上周
SELECT * FROM Date where DATEDIFF(week,dateday,GETDATE())=1
查询本月
SELECT * FROM Date where DATEDIFF(month,dateday,GETDATE())=0
查询上月
SELECT * FROM Date where DATEDIFF(month,dateday,GETDATE())=1
查询本季度的
SELECT * FROM Date where DATEDIFF(quarter,dateday,GETDATE())=0
查询上季度的
SELECT * FROM Date where DATEDIFF(quarter,dateday,GETDATE())=1
查询本年的
SELECT * FROM Date where DATEDIFF(year,dateday,GETDATE())=0
查询上年的
SELECT * FROM Date where DATEDIFF(year,dateday,GETDATE())=1