I have tried solution for this is like.
select dateadd(wk, datediff(wk, 0, getdate()), 0)as StartDate ,
(select dateadd(wk, datediff(wk, 0, getdate()), 0) + 5) as EndDate
it gives monday-saturday in result, but on Sunday it gives me next week days
I want sunday as last day of week and Monday as First Day of week..
Please Help...
6条答案
按热度按时间6yjfywim1#
In general, use
SET DATEFIRST 1
to specify that monday is the first day of the week. However, that doesn't solve the issue here. Use this syntax instead:Demo
SET DATEFIRST (Transact-SQL)
zte4gxcn2#
You just add 6 days instead of 5.
qlvxas9a3#
kse8i1jr4#
This may be excessively complicated, but it was tons of fun.
--This first part is to get the most recently occurred monday.
--It starts by creating a table which will hold all dates until the most recent monday, then sets the min of that table to the @mondaythisweek variable.
--This part creates a table from @mondaythisweek to the next sunday
prdp8dxp5#
With Time
zi8p0yeb6#
CREATE PROCEDURE forweek
( @year varchar(max) --,@SearchParam varchar(max)
)
AS BEGIN Declare @StartDate date, @d date, @f date, @l date, @currentyear varchar(max);
if ( @year = @currentyear) begin SET @d = dateadd(week,datediff(week,0,getdate())-datediff(wk,@year+'-01-01',getdate()+1),0) --start of week from a year ago SET @f = dateadd(week,datediff(week,0,getdate()-1),0) --start of current partial week; SET @l = dateadd(week,datediff(week,0,getdate())-datediff(wk,@year+'-01-01',getdate()+1),6) create table #weeks (dateid int IDENTITY(1,1) PRIMARY KEY, week_starting date , week_End date )
while @d <= @f begin insert into #weeks (week_starting,week_End) values (@d,@l) set @d = dateadd(week,1,@d) set @l = dateadd(week,1,@l) end --select * from #weeks Select (convert(varchar, w.week_starting, 101)+'-'+ convert(varchar, w.week_End, 101)) as weekrange, w.week_starting as weekstarting ,w.week_End as weekEnd from #weeks w order by w.dateid desc --where (convert(varchar, w.week_starting, 101)+'-'+ convert(varchar, w.week_End, 101)) like '%' + @SearchParam + '%'
drop table #weeks end
else begin SET @d = dateadd(week,datediff(week,0,@year+'-12-31')-datediff(wk,@year+'-01-01',@year+'-12-31')+1,0) --start of week from a year ago SET @f = dateadd(week,datediff(week,0,@year+'-12-31'),6) --start of current partial week; SET @l = dateadd(week,datediff(week,0,@year+'-12-31')-datediff(wk,@year+'-01-01',@year+'-12-31')+1,6) create table #weeks1 (dateid int IDENTITY(1,1) PRIMARY KEY, week_starting date , week_End date )
while @d <= @f begin insert into #weeks1 (week_starting,week_End) values (@d,@l) set @d = dateadd(week,1,@d) set @l = dateadd(week,1,@l) end --select * from #weeks1 Select (convert(varchar, w.week_starting, 101)+'-'+ convert(varchar, w.week_End, 101)) as weekrange, w.week_starting as weekstarting ,w.week_End as weekEnd from #weeks1 w --where (convert(varchar, w.week_starting, 101)+'-'+ convert(varchar, w.week_End, 101)) like '%' + @SearchParam + '%' order by w.dateid desc drop table #weeks1
end