How to find comming sunday to saturday record.

[brahman]

I am writing a sql query which can fetch me record ranging from comming sunday to saturday.
the search date can be any date and the sunday should be the very next coming sunday.
or in other simple words i want the records for the coming week starting from sunday and ending at saturday.

i am able to write a query for this bu the problem is i am not able to get the next coming sunday date with respect to the sysdate.

someone if come across this thing before kindly put some light.

bye for now
amit

 

[ClaireHCM]

Here I use getdate,You can replace with any date you need.

select dateadd(d,8-datepart(dw,getdate()),getdate()) as Sun
select dateadd(d,9-datepart(dw,getdate()),getdate()) as Mon
select dateadd(d,10-datepart(dw,getdate()),getdate()) as Tue
select dateadd(d,11-datepart(dw,getdate()),getdate()) as Wed
select dateadd(d,12-datepart(dw,getdate()),getdate()) as Thu
select dateadd(d,13-datepart(dw,getdate()),getdate()) as Fri
select dateadd(d,14-datepart(dw,getdate()),getdate()) as Sat

 

[brahman]

thanx a lot boss for the immediate and accurate reply.
Well now i need to modify it again for the loop i.e. on every request a user should get the next weeks record.
The result starts with the comming sunday to saturday but for next request it should be the second sunday to second satruday.


thanx once again.

bye for now
amit

 

[JohnEfford]

Copy and past the query below, into query analysiser to see if it gives you the correct date values.

Select
LastWeek=DateAdd(day, -(datepart(weekday, GetDate())),GetDate()),
NextWeek=DateAdd(day, -(datepart(weekday, GetDate()) - 7),GetDate())

If it does Then:

Select *
From YourTable
Where YourDateFeild Between
DateAdd(day, -(datepart(weekday, GetDate())),GetDate())
And
DateAdd(day, -(datepart(weekday, GetDate()) - 7),GetDate())

 

[ClaireHCM]

The following query is for next weeks record

declare @count int
declare @dw varchar(3)
set @count = 8
while @count <=14
begin
if @count = 8 set @dw = 'Sun'
if @count = 9 set @dw = 'Mon'
if @count = 10 set @dw = 'Tue'
if @count = 11 set @dw = 'Wed'
if @count = 12 set @dw = 'Thu'
if @count = 13 set @dw = 'Fri'
if @count = 14 set @dw = 'Sat'

exec ('select dateadd(d,'+@count+'-datepart(dw,getdate()),getdate()) as '+@dw)
set @count = @count +1
end

-----------------------------------------------------------
For &quot;second sunday to second satruday&quot;

declare @count int
declare @dw varchar(3)
set @count = 15
while @count <=21
begin
if @count = 15 set @dw = 'Sun'
if @count = 16 set @dw = 'Mon'
if @count = 17 set @dw = 'Tue'
if @count = 18 set @dw = 'Wed'
if @count = 19 set @dw = 'Thu'
if @count = 20 set @dw = 'Fri'
if @count = 21 set @dw = 'Sat'

exec ('select dateadd(d,'+@count+'-datepart(dw,getdate()),getdate()) as '+@dw)
set @count = @count +1
end

 

[brahman]

thanx a lot for all the responses, now the problem is solved by the tem members cooperationa nd quick response.
I thank ClaireHsu and JohnEfford for there valuable suggestions.

claireHsu , since i am using htis query with JSP and beans so for the other weeks i cant use the procedure kind solution you have given i'll put a variable in my this query and than pass it for every request.
-----------------------------------
select x_date from x_view where
x_date between dateadd(d,7-datepart(dw,getdate()),getdate()) and
dateadd(d,14-datepart(dw,getdate()),getdate())
-----------------------------------
now for this query i am planning to set a variable counter and pass it everytime for next request.

int count=7;
-----------------------------------
select x_date from x_view where
x_date between dateadd(d,count-datepart(dw,getdate()),getdate()) and
dateadd(d,7+count-datepart(dw,getdate()),getdate())
-----------------------------------
and for every request this count increase or decrease, is this method ok or there is anythign better than this.
bye for now
amit