sql 关于日期的代码

取每个月的总和(每年都有)

select   sum(isnull(PAYMENT,0)) as total,convert(char(06),C_CKSJ,112),K_ID as date from SALES   group by convert(char(06),C_CKSJ,112),K_ID

 

如何在SQL Server中查询本周 本月 本期, 本年的记录

表名为：tableName
时间字段名为：theDate
=====================
datePart函数 日期部分缩写yearyy, yyyyquarterqq, qmonthmm, mdayofyeardy, ydaydd, dweekwk, wwweekdaydwHourhhminutemi, nsecondss, smillisecondms
查询本月的记录
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函数
日期部分缩写yearyy, yyyyquarterqq, qMonthmm, mdayofyeardy, yDaydd, dWeekwk, wwHourhhminutemi, nsecondss, smillisecondms
查询本日的记录
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)