1. 查询当日、昨日、本周、本月、本季度、本年数据
-- 查询今日数据
select count(0) todayCount from alarm_data where DATEDIFF(dd,upload_date,GETDATE())=0
select count(0) todayCount from alarm_data where CONVERT(VARCHAR(10),upload_date,120)=CONVERT(VARCHAR(10),GETDATE(),120)
-- 查询昨日数据
select count(0) todayCount from alarm_data where DATEDIFF(dd,upload_date,GETDATE()-1)=0
select count(0) yesterdayCount from alarm_data where CONVERT(VARCHAR(10),upload_date,120)=CONVERT(VARCHAR(10),GETDATE()-1,120)
-- 查询本周数据
select count(0) weekCount from alarm_data where upload_date BETWEEN DATEADD( week, DATEDIFF( week, 0, GETDATE() - 1 ), 0 ) AND GETDATE()
-- 查询本月数据
select count(0) monthCount from alarm_data where DATEDIFF(month,upload_date,GETDATE())=0
select count(0) monthCount from alarm_data where CONVERT(VARCHAR(7),upload_date,120)=CONVERT(VARCHAR(7),GETDATE(),120)
-- 查询本季度数据
select count(0) quarterCount from alarm_data where DATEPART(QQ,upload_date)=DATEPART(QQ,GETDATE()) and DATEPART(YY,upload_date)=DATEPART(YY,GETDATE())
-- 查询本年数据
select count(0) yearCount from alarm_data where DATEPART(YY,upload_date)=DATEPART(YY,GETDATE())
select count(0) yearCount from alarm_data where CONVERT(VARCHAR(4),upload_date,120)=CONVERT(VARCHAR(4),GETDATE(),120)
select count(0) yearCount from alarm_data where year(upload_date)=year(getdate())
2. 获取其他日期数据
-- 获取今天日期(年-月-日)
select 今天日期 = CONVERT(VARCHAR(10),GETDATE(),120)
select 今天日期 = CONVERT(DATETIME,GETDATE(),120)
-- 获取今日时间 (年-月-日 时:分:秒:毫秒)
select 今日时间 = GETDATE()
-- 获取昨天日期
select 昨天日期 = CONVERT(VARCHAR(10),GETDATE()-1,120)
-- 获取本周指定星期的日期
select 本周周一 = DATEADD( week, DATEDIFF( week, 0, GETDATE() - 1 ), 0 )
select 本周周日 = DATE