Nextday generation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Nextday generation

Author

Topic

learntsql

524 Posts

Posted - 2011-08-04 : 03:52:53

Hi All,

I have a requirement to add "X" no. of weeks for latest date once i get that date i have get the date based on second parameter.
The second parameter is weekname.

for Eg:
latestdate is 27-jul-2011
add 1 week -->03-Aug-2011

and second parameter is Sat'day
now i have to get the next sat'day day from here.
TIA.

learntsql

524 Posts

Posted - 2011-08-04 : 06:05:41

Experts any idea plz....!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-04 : 06:37:44

Look up DATEADD and DATEPART.

And have some patience. We're all volunteers here, we all have our own jobs and our own problems and post in our spare time. Bumping your post after 2 hours is entirely unnecessary.

--
Gail Shaw
SQL Server MVP

learntsql

524 Posts

Posted - 2011-08-04 : 07:00:20

Hey I am sorry,

I didnt mean that.
anyhow i could manage it.
Thanks for your cocern.

lappin
Posting Yak Master

182 Posts

Posted - 2011-08-04 : 07:29:29

I'm sure some-one could do a better way - but this works:

Create Procedure getWkDay @DyName as varchar(10),@iNoWeeks as integer
AS
--Usage: exec getWkDay 'Saturday',1
declare @iDy as integer

declare @Today as date = getdate()
declare @NewDt as date
declare @increment as integer

declare @StartDayNo as integer =datepart(dw,@Today)

declare @DayDiff as integer

set @NewDt =DATEADD(week,@iNoWeeks,@Today)
SELECT @iDy =
CASE @DyName
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
WHEN 'Sunday' THEN 7

ELSE -99
END

If @iDy>@StartDayNo
begin

set @DayDiff = ( @iDy - @StartDayNo )
end
If @iDy<=@StartDayNo
begin

set @DayDiff = 7+ ( @iDy - @StartDayNo )
end

select DATEADD(DD,@DayDiff, @NewDt) as NextDy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources