一、SQL获取本日、本周、本月、本季度、本年数据
本日:SELECT * FROM TableName WHERE DATEDIFF(dd, 日期字段, GETDATE()) = 0;
本月:SELECT * FROM TableName WHERE DATEDIFF(mm, 日期字段, GETDATE()) = 0;
本周:SELECT * FROM TableName WHERE DATEDIFF(week, 日期字段, GETDATE()) = 0;
本季:SELECT * FROM TableName WHERE DATEDIFF(qq, 日期字段, GETDATE()) = 0;
本年:SELECT * FROM TableName WHERE DATEDIFF(yy, 日期字段, GETDATE()) = 0;
二、只获取日期
SELECT CONVERT(VARCHAR(10), GETDATE(), 120); --当前日期 2020-03-23
SELECT CONVERT(VARCHAR(10), GETDATE(), 121); --当前日期 2020-03-23
SELECT CONVERT(VARCHAR(10), GETDATE() - 1, 120); --前一日 2020-03-22
SELECT CONVERT(VARCHAR(10), GETDATE() + 1, 120); --后一日 2020-03-24
三、关于日期在表中查询
昨天:SELECT * FROM TableName WHERE DATEDIFF(dd, DateTimCol, GETDATE()) = 1;
明天:SELECT * FROM TableName WHERE DATEDIFF(dd, GETDATE(), DateTimCol) = 1;
最近7天:SELECT * FROM TableName WHERE DATEDIFF(dd, 时间字段, GETDATE()) <= 7;
上周:SELECT * FROM TableName WHERE DATEDIFF(week , 时间字段,GETDATE()) = 1
本周:SELECT * FROM TableName WHERE DATEDIFF(week , 时间字段,GETDATE()) = 0
下周:SELECT * FROM TableName WHERE DATEDIFF(week , 时间字段,GETDATE()) = -1
上月:SELECT * FROM TableName WHERE MONTH(日期字段) = MONTH(GETDATE()) - 1;
本月:SELECT * FROM TableName WHERE MONTH(日期字段) = MONTH(GETDATE());
下月:SELECT * FROM TableName WHERE MONTH(日期字段) = MONTH(GETDATE()) + 1;
更多推荐
SQL日期查询汇总
发布评论