今天: SELECT * FROM TABLENAME WHERE DATEFIELD>=CONBERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112)) AND DATEFIELD<DATEADD(DAY,1,CONBERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112)))本周: SELECT * FROM TABLENAME WHERE YEAR(DATEFIELD)=YEAR(GETDATE()) AND DATEPART(WW,DATEFIELD)=DATEPART(WW,GETDATE())上周:SELECT * FROM TABLENAME WHERE YEAR(DATEFIELD)=YEAR(GETDATE()) AND DATEPART(WW,DATEFIELD)=DATEPART(WW,GETDATE())-1
where 日期字段=getdate()--今天 where datepart(weekday,日期字段)=datepart(weekday,getdate())--本周
更正上周: SELECT * FROM TABLENAME WHERE (YEAR(DATEFIELD)=YEAR(GETDATE()) AND DATEPART(WW,DATEFIELD)=DATEPART(WW,GETDATE())-1 ) OR (YEAR(DATEFIELD)=YEAR(GETDATE())-1 AND DATEPART(WW,GETDATE())=1 AND DATEPART(WW,DATEFIELD)=DATEPART(WW,CAST(YEAR(GETDATE())-1 AS VARCHAR(10))+'-12-31') )
本周: SELECT * FROM TABLENAME WHERE DATEFIELD>=CONBERT(DATETIME,CONVERT(CHAR(8),DATEADD(DAY,1-DATEPART(WEEKDAY,GETDATE()),GETDATE()),112)) AND DATEFIELD<CONBERT(DATETIME,CONVERT(CHAR(8),DATEADD(DAY,9-DATEPART(WEEKDAY,GETDATE()),GETDATE()) ,GETDATE()),112))上周: SELECT * FROM TABLENAME WHERE DATEFIELD>=CONBERT(DATETIME,CONVERT(CHAR(8),DATEADD(DAY,-6-DATEPART(WEEKDAY,GETDATE()),GETDATE()),112)) AND DATEFIELD<CONBERT(DATETIME,CONVERT(CHAR(8),DATEADD(DAY,2-DATEPART(WEEKDAY,GETDATE()),GETDATE()) ,GETDATE()),112))
解决了,再次谢谢大家本周的写法 select * from table1 where datefield >=CONVERT(datetime, CONVERT(varchar(10),dateadd(day,-(datepart(dw,getdate())-1),getdate()),101)) and datefield < CONVERT(datetime, CONVERT(varchar(10),dateadd(day,7-(datepart(dw,getdate())-1),getdate()),101)) 这么繁琐是为了严密(去掉时间部分),为了便于大家阅读我又写了一个核心版select * from table1 where datefield >=dateadd(day,-(datepart(dw,getdate())-1),getdate()) and datefield < dateadd(day,7-(datepart(dw,getdate())-1),getdate()这样写可能就躲过了跨年份的问题。不过我还是想向大家请教怎么把时间去掉,我用了convert转换,我觉得我的写法很繁琐,有没有不使用convert或cast的简单写法?高手指教 例如2002-09-25 10:16:51.530去掉时间部分变成2002-09-25 00:00:00.000,其实还是“今天”的问题。 “上周”等同于“本周的写法”
to Yang_(扬帆破浪):哦,发贴后才看到你的新贴,谢谢。仔细看了一下,我们写的基本相同,不过那个9是不是应该改成8,不过知道思路就行了,谢谢能不能在帮我想想去掉时间的写法,不用转化函数,一会儿结贴
本周 declare @dat datetime,@fdat datetime,@ldat datetime set @dat='2002-12-30'select @fdat=dateadd(weekday,-datepart(weekday,@dat)+2,@dat) select @ldat=dateadd(weekday,-datepart(weekday,@dat)+8,@dat)select * from t1 where datediff(weekday,@fdat,a)>=0 and datediff(weekday,a,@ldat)>=0完全不考虑年份你的写法有潜在问题,就是时间上可能会漏掉一些记录增加一些记录。今天 select * from t1 where datediff(weekday,@dat,a)=0 and datediff(day,@dat,a)>=-1 and datediff(day,@dat,a)<=1
今天 select * from t1 where year(@dat)=year(@a) and datediff(dayofyear,@dat,a)=0
SELECT * FROM TABLENAME
WHERE DATEFIELD>=CONBERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112))
AND DATEFIELD<DATEADD(DAY,1,CONBERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112)))本周:
SELECT * FROM TABLENAME
WHERE YEAR(DATEFIELD)=YEAR(GETDATE())
AND DATEPART(WW,DATEFIELD)=DATEPART(WW,GETDATE())上周:SELECT * FROM TABLENAME
WHERE YEAR(DATEFIELD)=YEAR(GETDATE())
AND DATEPART(WW,DATEFIELD)=DATEPART(WW,GETDATE())-1
where datepart(weekday,日期字段)=datepart(weekday,getdate())--本周
SELECT * FROM TABLENAME
WHERE (YEAR(DATEFIELD)=YEAR(GETDATE())
AND DATEPART(WW,DATEFIELD)=DATEPART(WW,GETDATE())-1
)
OR
(YEAR(DATEFIELD)=YEAR(GETDATE())-1
AND DATEPART(WW,GETDATE())=1
AND DATEPART(WW,DATEFIELD)=DATEPART(WW,CAST(YEAR(GETDATE())-1 AS VARCHAR(10))+'-12-31')
)
SELECT * FROM TABLENAME
WHERE DATEFIELD>=CONBERT(DATETIME,CONVERT(CHAR(8),DATEADD(DAY,1-DATEPART(WEEKDAY,GETDATE()),GETDATE()),112))
AND DATEFIELD<CONBERT(DATETIME,CONVERT(CHAR(8),DATEADD(DAY,9-DATEPART(WEEKDAY,GETDATE()),GETDATE())
,GETDATE()),112))上周:
SELECT * FROM TABLENAME
WHERE DATEFIELD>=CONBERT(DATETIME,CONVERT(CHAR(8),DATEADD(DAY,-6-DATEPART(WEEKDAY,GETDATE()),GETDATE()),112))
AND DATEFIELD<CONBERT(DATETIME,CONVERT(CHAR(8),DATEADD(DAY,2-DATEPART(WEEKDAY,GETDATE()),GETDATE())
,GETDATE()),112))
select * from table1
where datefield >=CONVERT(datetime, CONVERT(varchar(10),dateadd(day,-(datepart(dw,getdate())-1),getdate()),101))
and datefield < CONVERT(datetime, CONVERT(varchar(10),dateadd(day,7-(datepart(dw,getdate())-1),getdate()),101)) 这么繁琐是为了严密(去掉时间部分),为了便于大家阅读我又写了一个核心版select * from table1
where datefield >=dateadd(day,-(datepart(dw,getdate())-1),getdate())
and datefield < dateadd(day,7-(datepart(dw,getdate())-1),getdate()这样写可能就躲过了跨年份的问题。不过我还是想向大家请教怎么把时间去掉,我用了convert转换,我觉得我的写法很繁琐,有没有不使用convert或cast的简单写法?高手指教
例如2002-09-25 10:16:51.530去掉时间部分变成2002-09-25 00:00:00.000,其实还是“今天”的问题。
“上周”等同于“本周的写法”
declare @dat datetime,@fdat datetime,@ldat datetime
set @dat='2002-12-30'select @fdat=dateadd(weekday,-datepart(weekday,@dat)+2,@dat)
select @ldat=dateadd(weekday,-datepart(weekday,@dat)+8,@dat)select * from t1
where datediff(weekday,@fdat,a)>=0
and datediff(weekday,a,@ldat)>=0完全不考虑年份你的写法有潜在问题,就是时间上可能会漏掉一些记录增加一些记录。今天
select * from t1
where datediff(weekday,@dat,a)=0 and datediff(day,@dat,a)>=-1 and
datediff(day,@dat,a)<=1
select * from t1
where year(@dat)=year(@a) and datediff(dayofyear,@dat,a)=0