SQL Server中查询本周 本月 本期, 本年的记录 本月统计、本周统计和本季度

表名为：tableName 时间字段名为：theDate ===================== datePart函数

日期部分	缩写
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
Hour	hh
minute	mi, n
second	ss, s
millisecond	ms

查询本月的记录 select * from tableName where DATEPART(mm, theDate) = DATEPART(mm, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
查询本周的记录 select * from tableName where DATEPART(wk, theDate) = DATEPART(wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
查询本季的记录 select * from tableName where DATEPART(qq, theDate) = DATEPART(qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
其中:GETDATE()是获得系统时间的函数。

 

-------------------------------------------------------------------------------------------------------------------------------------------------- datediff函数

日期部分	缩写
year	yy, yyyy
quarter	qq, q
Month	mm, m
dayofyear	dy, y
Day	dd, d
Week	wk, ww
Hour	hh
minute	mi, n
second	ss, s
millisecond	ms

查询本日的记录 select count(*) from tableName where (DATEDIFF(dd, theDate, GETDATE()) = 0) 查询本月的记录 select count(*) from tableName where (DATEDIFF(mm, theDate, GETDATE()) = 0) 查询本年的记录 select count(*) from tableName where (DATEDIFF(yy, theDate, GETDATE()) = 0)

 

文章来源：http://www.bcbbs.net/news/Content.aspx?id=34330 文章来源：http://www.bcbbs.net/news/Content.aspx?id=34330

 

 

表名为：tableName 时间字段名为：theDate

查询本月的记录 select * from tableName where DATEPART(mm, theDate) = DATEPART(mm, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())

查询本周的记录 select * from tableName where DATEPART(wk, theDate) = DATEPART(wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())

查询本季的记录 select * from tableName where DATEPART(qq, theDate) = DATEPART(qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())

其中:GETDATE()是获得系统时间的函数。