=====================
表名为：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(dd, theDate) = DATEPART(dd, GETDATE()) and 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 left(convert(char,CollectTime ,23),7) = left(convert(char,dateadd(mm,-1,getdate()),23),7)
查询本月的记录
select * from tableName where DATEPART(mm, theDate) = DATEPART(mm, 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())

查询本年的记录
select * from tableName where 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(wk, theDate, GETDATE()) = 0)
查询本月的记录
select count(*) from tableName where (DATEDIFF(mm, theDate, GETDATE()) = 0)
查询本季的记录
select count(*) from tableName where (DATEDIFF(qq, theDate, GETDATE()) = 0)
查询本年的记录
select count(*) from tableName where (DATEDIFF(yy, theDate, GETDATE()) = 0)