Sql Server中查询今天、昨天、本周、上周、本月、上月数据以及周的起始时间

DATEDIFF ( datepart , startdate , enddate )
释义：计算时间差
datepare值：year | quarter | month | week | day | hour | minute | second | millisecond
startdate：开始日期
enddate ：结束日期
GetDate()
释义：获取当前的系统日期

下面例子中表名为tablename,条件字段名为inputdate
查询今天

SELECT * FROM tablename where DATEDIFF(day,inputdate,GETDATE())=0
查询昨天

SELECT * FROM tablename where DATEDIFF(day,inputdate,GETDATE())=1
查询本周

SELECT * FROM tablename where datediff(week,inputdate,getdate())=0
查询上周

SELECT * FROM tablename where datediff(week,inputdate,getdate())=1
查询本月

SELECT * FROM tablename where DATEDIFF(month,inputdate,GETDATE())=0
查询上月

SELECT * FROM tablename where DATEDIFF(month,inputdate,GETDATE())=1

查询本年

select * from Keywords  where datediff(year, Addtime,getdate())=0

获取周的起始日期：

当前时间周的起始日期bai（以周一为例）select DATEADD(week,DATEDIFF(week,0,getdate()),0)
上周起始：daoselect dateadd(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0))
上上周起始：select dateadd(week,-2,DATEADD(week,DATEDIFF(week,0,getdate()),0))
上上上周起始：select dateadd(week,-3,DATEADD(week,DATEDIFF(week,0,getdate()),0))
以此类推

1、获取指定日期的数据

DATEDIFF(day,CONVERT(datetime,日期字段,101),指定的日期) = 0;