select dateadd(d,-1, DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0))
union
select dateadd(d,-1,dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+1, 0)))
union
select dateadd(d,-1, DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+1, 0))
union
select dateadd(d,-1,dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+2, 0)))
union
select dateadd(d,-1, DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+2, 0))
union
select dateadd(d,-1,dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+3, 0)))
union
select dateadd(d,-1, DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+3, 0))
union
select dateadd(d,-1,dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+4, 0)))上述语句我得到的是每月的第一、二、三、四周的第一天和最后一天,但有时会出现问题,比如说八月份,其实我想得到的第一周应该是:7月30-8月5,而上面的语句却是从8月6-8月12開始,请问这个问题怎么解决?
union
select dateadd(d,-1,dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+1, 0)))
union
select dateadd(d,-1, DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+1, 0))
union
select dateadd(d,-1,dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+2, 0)))
union
select dateadd(d,-1, DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+2, 0))
union
select dateadd(d,-1,dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+3, 0)))
union
select dateadd(d,-1, DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+3, 0))
union
select dateadd(d,-1,dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()))+4, 0)))上述语句我得到的是每月的第一、二、三、四周的第一天和最后一天,但有时会出现问题,比如说八月份,其实我想得到的第一周应该是:7月30-8月5,而上面的语句却是从8月6-8月12開始,请问这个问题怎么解决?
关键是我SQL语句怎么去搞定,能否告之
set @firstday=convert(datetime,convert(char(8),getdate(),120)+'1')
--使用时可将getdate()换成你要求的日期.select dateadd(day,-datepart(dw,@firstday),@firstday)
union
select dateadd(day,7-datepart(dw,@firstday),@firstday)
union
select dateadd(day,8-datepart(dw,@firstday),@firstday)
union
select dateadd(day,14-datepart(dw,@firstday),@firstday)
union
select dateadd(day,15-datepart(dw,@firstday),@firstday)
union
select dateadd(day,21-datepart(dw,@firstday),@firstday)
union
select dateadd(day,22-datepart(dw,@firstday),@firstday)
union
select dateadd(day,28-datepart(dw,@firstday),@firstday)--结果为
2006-07-29 00:00:00.000
2006-08-05 00:00:00.000
2006-08-06 00:00:00.000
2006-08-12 00:00:00.000
2006-08-13 00:00:00.000
2006-08-19 00:00:00.000
2006-08-20 00:00:00.000
2006-08-26 00:00:00.000
谢了,马上给分。
如:
declare @firstday datetime
set @firstday=convert(datetime,convert(char(8),getdate(),120)+'1')
--使用时可将getdate()换成你要求的日期.select @firstday=dateadd(day,-datepart(dw,@firstday),@firstday)
union
select dateadd(day,6,@firstday)
union
select dateadd(day,7,@firstday)
...