sql得到当前系统时间得 日期部分
--如果是在表中查詢
--昨天
Select * From TableName Where DateDiff(dd, DateTimCol, GetDate()) = 1
--明天
Select * From TableName Where DateDiff(dd, GetDate(), DateTimCol) = 1
--最近七天
Select * From TableName Where DateDiff(dd, DateTimCol, GetDate()) <= 7
--随后七天
Select * From TableName Where DateDiff(dd, GetDate(), DateTimCol) <= 7
--上周
Select * From TableName Where DateDiff(wk, DateTimCol, GetDate()) = 1
--本周
Select * From TableName Where DateDiff(wk, DateTimCol, GetDate()) = 0
--下周
Select * From TableName Where DateDiff(wk, GetDate(), DateTimCol ) = 1
--上月
Select * From TableName Where DateDiff(mm, DateTimCol, GetDate()) = 1
--本月
Select * From TableName Where DateDiff(mm, DateTimCol, GetDate()) = 0
--下月
Select * From TableName Where DateDiff(mm, GetDate(), DateTimCol ) = 1
--------------------------------------------------------
本周
select * from tb where datediff(week , 时间字段 ,getdate()) = 0
上周
select * from tb where datediff(week , 时间字段 ,getdate()) = 1
下周
select * from tb where datediff(week , 时间字段 ,getdate()) = -1
--------------------------------------------------------
select convert(varchar(10),getdate(),120)
昨天
select convert(varchar(10),getdate() - 1,120)
明天
select convert(varchar(10),getdate() + 1,120)
最近七天
select * from tb where 时间字段 >= convert(varchar(10),getdate() - 7,120)
随后七天
select * from tb where 时间字段 <= convert(varchar(10),getdate() + 7,120) and 时间字段 >= 时间字段
上月
select * from tb where month(时间字段) = month(getdate()) - 1
本月
select * from tb where month(时间字段) = month(getdate())
下月
select * from tb where month(时间字段) = month(getdate()) + 1
本周
select * from tb where datediff(week , 时间字段 ,getdate()) = 0
本周 周一开始
select * from u_user_Current_month_Score_Got_Log where
convert(char(10),insertTime,120)>=convert(char(10),DATEADD(wk, DATEDIFF(wk,0,getdate()), 0),120)
and convert(char(10),insertTime,120)<=convert ( char(10),DATEADD( wk, DATEDIFF(wk,0,getdate()), 6 ),120 )
第一种 本周 周一开始
set datefirst 1 --以周1为第一天
select * From u_user_Current_month_Score_Got_Log
where datepart(week,inserttime) = datepart(week,dateadd(day,-1,getdate()))
第二种 本周 周一开始
select * From u_user_Current_month_Score_Got_Log
where datepart(week,dateadd(day,-1,inserttime)) = datepart(week,dateadd(day,-1,getdate()))
上周 周一开始
select * From u_user_Current_month_Score_Got_Log
where datepart(week,dateadd(day,-1,inserttime)) = datepart(week,dateadd(day,-1,getdate()))-1
上周
select * from tb where datediff(week , 时间字段 ,getdate()) = 1
下周
select * from tb where datediff(week , 时间字段 ,getdate()) = -1
--------------------------------------------------------
昨天：dateadd(day,-1,getdate())
明天：dateadd(day,1,getdate())
上月：month(dateadd(month, -1, getdate()))
本月：month(getdate())
下月：month(dateadd(month, 1, getdate()))