Listing Next 5 Sunday

Again I got an opportunity to write an interesting piece of College Code in response to the question posted on one of my SQL Server forum. The problem statement was

“I want a list of next five Sunday, how I can achieve this through T-SQL”

Ya its very simple, just a couple of Date and String function, blend it with college logic, have your tea with Parle G and that’s all code will all set to fly high. Enjoy Sunday J

So here I am sharing a piece of code which will do the rest, the code is self explanatory, So I don’t think I should buy my time to catch up anything from code to explain.

declare @dt datetime

declare @howMany int

declare @cntr int

set @cntr = 0

set @howMany = 6

set @dt = getdate()

create table #temp

(

SundayCol datetime,

WeekDayName varchar(10)

)

while (datename(w,@dt) not like 'Sunday')

begin

       set @dt= dateadd(dd,1,@dt)

end

--select datename(w,@dt)

insert into #temp

select @dt, Datename(w,@dt)

while (@cntr < @howMany)

begin

       set @dt = dateadd(dd,7,@dt)

       insert into #temp

       select @dt, Datename(w,@dt)

       set @cntr = @cntr + 1

end

select convert(varchar(10),SundayCol,103), WeekDayName from #temp

drop table #temp

Execute it and output you will get is next 6 Sunday. Enjoy your Sunday J

Date       WeekDayName

---------- -----------

29/06/2014 Sunday

06/07/2014 Sunday

13/07/2014 Sunday

20/07/2014 Sunday

27/07/2014 Sunday

03/08/2014 Sunday

10/08/2014 Sunday

The popping up of Sunday every week makes me happy and my potential double up to buys more time for my articles on SQL! J

 

Post Reference: Vikram Aristocratic Elfin Share