Last nth day of month - 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 2000 Forums

Transact-SQL (2000)

Last nth day of month

Author

Topic

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2004-09-16 : 12:51:18

I can get the last day of the month, but how do I find the last nth day of the month, like say, the last Monday of the month? Thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-16 : 13:03:17

[code]
Declare @today datetime,
@dayNum int

Set @today = convert(nvarchar,getdate(),101)

Set @dayNum = 2 --Monday is the 2nd day of the week

Select @today, dateadd(mm,1,@today-day(@today)) - (datepart(dw,dateadd(mm,1,@today-day(@today)))-@dayNum+7)%7
[/code]

Corey

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2004-09-16 : 13:11:43

Awesome, thanks!

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2004-09-16 : 13:42:53

One thing. if i set @today date to '10/1/2004' instead of this month, and i want the last Sunday (1), it returns the 24th, when it should be the 31st. Any clues? Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-16 : 14:14:35

I must have gotten it messed up somehow... sorry


Declare @today datetime,
	@dayNum int

Set @today = convert(nvarchar,getdate(),101)

Set @dayNum = 2 --Monday is the 2nd day of the week

Select @today, dateadd(mm,1,@today-day(@today)+1)-1 -(datepart(dw,dateadd(mm,1,@today-day(@today)+1)-1)-@dayNum+7)%7


Set @today = '10/1/2004'

Set @dayNum = 1 --Sunday is the 1st day of the week
	
Select @today, dateadd(mm,1,@today-day(@today)+1)-1 -(datepart(dw,dateadd(mm,1,@today-day(@today)+1)-1)-@dayNum+7)%7

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources