sqlserver 取昨天

sqlserver 取昨天

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