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()是獲得系統時間的函數。