在数据表中使用date列保存yyyy-MM-dd HH:MM:SS格式的时间记录,mssql中如何查询最近24小时、昨天、最近7天、上周、最近30天、上月的数据记录?
数据
我想要的结果是
day sum
1 0
2 3
3 0
4 0
5 1
6 0
7 6
.....
31 0
sql语句要怎么写啊
数据
我想要的结果是
day sum
1 0
2 3
3 0
4 0
5 1
6 0
7 6
.....
31 0
sql语句要怎么写啊
解决方案 »
- SQL Server 2005 的补丁问题
- 能否将条件带入存储过程?
- 自定义函数返回拼接SQL字符串可以顺利打印并执行但无法EXEC执行,调试也不行
- 简单问题咨询高手
- 问个SQL的问题
- 合并计算的计算过程
- 并发访问应该经常存在的吧?一读一写的时候。
- windows2000主域服务器安装sqlserver2000出现问题,十万火急,请各位大虾帮帮忙
- 谁有用VC++中ODBC API 编写数据库的例子?
- 更新语句问题,请各位高手进来指教
- 只提供表名,查询表中任意字段为空的数据
- sqlserver2000异常:查询处理器未能用优化程序生成查询计划,因为查询无法同时更新聚集键和 text、ntext 或 image 列。
DECLARE @Date DATETIME = GETDATE();
SELECT @Date AS '目前时间'
,DATEADD(DD,-1,@Date) AS '前一天'
,DATEADD(DD,1,@Date) AS '后一天'
/*月计算*/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通过月运算,保证日恒久为1号
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'--找到下月初再扣减1天,建议使用DATEADD而不要直接“-1”
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天'
/*周计算*/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此处与@@datefirst的值有关
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此处与@@datefirst的值有关
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此处与@@datefirst的值有关,其他天数类推
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'--注意此处与@@datefirst的值有关
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'--注意此处与@@datefirst的值有关
,DATENAME(WEEKDAY,@Date) AS '本日是周几'
,DATEPART(WEEKDAY,@Date) AS '本日是周几'--返回值 1-星期日,2-星期一,3-星期二......7-星期六
/*年度计算*/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/*季度计算*/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'
SELECT COUNT(id) AS cnt, DATEPART(mm, CAST(addtime AS datetime)) AS [Month]
FROM xhwodb_f.checks
WHERE (CAST(addtime AS datetime) BETWEEN '2012/04/08 09:10:43' AND
'2012/12/09 04:32:37')
GROUP BY DATEPART(mm, CAST(addtime AS datetime))得出的结果是:
cnt Month
23 6
16 9
28 11要怎么才能是下面的结果呢:
cnt Month
0 4
0 5
23 6
0 7
0 8
16 9
0 10
28 11
0 12