How to get next monday/tuesday from given date

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 2005 Forums

Transact-SQL (2005)

How to get next monday/tuesday from given date

Author

Topic

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-07 : 09:55:25

Hi,

i am new to sql server. i have one question ,
how to get next monday / tuesday or any perticuler day from given date.

Ex. suppose current date is 07 th may 2009 and day is Thursday and i want the next monday from this date ie. 11 may 2009
is there any built in function provided by SQL Server 2005.
( i know oracle next_day built in function returns the specified date)

any help appriciated.

Pankaj

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 10:01:52

select datename(dw,getdate())

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-07 : 10:06:32

quote:
Originally posted by sakets_2000

select datename(dw,getdate())

thanks for reply ,
it returns only name of the day but i want next monday date ??

i have write a function to achive this functionality please chk and please tell me if there is any built in function which will gives me same output.

CREATE FUNCTION NEXT_DAY ( @VDATE DATETIME,@VDAY VARCHAR(10))
RETURNS DATETIME
AS
BEGIN
DECLARE @I smallint
SET @I = 1
WHILE @I <= 7
BEGIN
IF DATENAME(WEEKDAY,DATEADD(D,@I,@VDATE)) = @VDAY
RETURN DATEADD(D,@I,@VDATE)
SET @I = @I + 1
END
RETURN DATEADD(D,@I,@VDATE)
END

Pankaj

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-05-07 : 10:58:55

Show some sample input data and expected results based on the input data. It could be something as simple as: select getdate() + 7

Question: Why are you using a while loop?

Terry

-- Procrastinate now!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 12:03:46

This should work I think..
replace getdate() with your date variable..

select 
	case 	when datename(dw,dateadd(day,1,getdate()))='Monday' then dateadd(day,1,getdate())
		when datename(dw,dateadd(day,2,getdate()))='Monday' then dateadd(day,2,getdate())
		when datename(dw,dateadd(day,3,getdate()))='Monday' then dateadd(day,3,getdate())
		when datename(dw,dateadd(day,4,getdate()))='Monday' then dateadd(day,4,getdate())
		when datename(dw,dateadd(day,5,getdate()))='Monday' then dateadd(day,5,getdate())
		when datename(dw,dateadd(day,6,getdate()))='Monday' then dateadd(day,6,getdate())
		when datename(dw,dateadd(day,7,getdate()))='Monday' then dateadd(day,7,getdate())
end

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-08 : 00:07:14

quote:
Originally posted by tosscrosby

Show some sample input data and expected results based on the input data. It could be something as simple as: select getdate() + 7

Question: Why are you using a while loop?

First, sorry for late reply,

actually what i want is when i pass inputs to the function it will give me next date for given day

suppose i pass

select dbo.next_day(getdate(),'Monday')

then it should gives me 11/05/2009

if i pass
select dbo.next_day(getdate(),'Thursday')

then it should give me 14/05/2009.

Terry

-- Procrastinate now!

Pankaj

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-08 : 00:08:42

quote:

Originally posted by sakets_2000

This should work I think..
replace getdate() with your date variable..

select 
	case 	when datename(dw,dateadd(day,1,getdate()))='Monday' then dateadd(day,1,getdate())
		when datename(dw,dateadd(day,2,getdate()))='Monday' then dateadd(day,2,getdate())
		when datename(dw,dateadd(day,3,getdate()))='Monday' then dateadd(day,3,getdate())
		when datename(dw,dateadd(day,4,getdate()))='Monday' then dateadd(day,4,getdate())
		when datename(dw,dateadd(day,5,getdate()))='Monday' then dateadd(day,5,getdate())
		when datename(dw,dateadd(day,6,getdate()))='Monday' then dateadd(day,6,getdate())
		when datename(dw,dateadd(day,7,getdate()))='Monday' then dateadd(day,7,getdate())
end

Thanks saket.

Pankaj

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-08 : 04:34:07

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 04:54:47

[code]
select dateadd(week, datediff(week, 0, getdate()), 7)
[/code]

for Tuesday, change from 7 to 8

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources