sql时间查询 按 月 季 年（1）

查询本日的记录 select * from tableName where DATEPART(dd, theDate) = DATEPART(dd, GETDATE()) and DATEPART(mm, theDate) = DATEPART(mm, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE()) 查询本月的记录 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())
查询 n 天前的记录 select * from tableName where DATEDIFF(DAY,theDate,GETDATE())=n
查询 n 年前的记录 select * from tableName where DATEDIFF(Year,theDate,GETDATE())=n
查询 n 月前的记录 select * from tableName where DATEDIFF(Month,theDate,GETDATE())=n
===================== 表名为：tableName
时间字段名为：theDate GETDATE()是获得系统时间的函数。 ===================== 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

 

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