How to find third monday from year

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

General SQL Server Forums

New to SQL Server Programming

How to find third monday from year

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2011-03-28 : 08:43:09

Hi All,
I am having @Years varchar(8000) as parameter
@Years - '1999,2000,2001,2002,2003'
How can I get date of third monday of January for all these years
Result should be in such a way that

Result = '17-01-1999,19-01-2000,...'

Thanks & Regards
Binto Thomas

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-28 : 09:02:50

A very odd request...


Declare @Years varchar(8000),
	@mondays varchar(8000)

Set @Years = '1999,2000,2001,2002,2003'


--With Monday = 2

Select 
	@mondays = isnull(@mondays+',','') +
	convert(varchar,
		case when DATEPART(dw,dt) <= 2 
			then 2-DATEPART(dw,dt) + 14
		else 9-DATEPART(dw,dt) + 14
		end	+ dt,
		103)
From
	(
	Select
		dt=CONVERT(datetime,'1/1/'+CONVERT(varchar,yr))
	From 
		(select yr=number+1950 from master..spt_values where type = 'p' and number between 1 and 100) A
	Where ','+@Years+',' like '%,'+CONVERT(varchar,yr)+',%'
	) Z

Select @mondays

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

binto
Yak Posting Veteran

59 Posts

Posted - 2011-03-29 : 07:26:23

Thanks a lot for your effort.Got the result exactly I expected.

quote:

Originally posted by Seventhnight

A very odd request...


Declare @Years varchar(8000),
	@mondays varchar(8000)

Set @Years = '1999,2000,2001,2002,2003'


--With Monday = 2

Select 
	@mondays = isnull(@mondays+',','') +
	convert(varchar,
		case when DATEPART(dw,dt) <= 2 
			then 2-DATEPART(dw,dt) + 14
		else 9-DATEPART(dw,dt) + 14
		end	+ dt,
		103)
From
	(
	Select
		dt=CONVERT(datetime,'1/1/'+CONVERT(varchar,yr))
	From 
		(select yr=number+1950 from master..spt_values where type = 'p' and number between 1 and 100) A
	Where ','+@Years+',' like '%,'+CONVERT(varchar,yr)+',%'
	) Z

Select @mondays

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

Thanks & Regards
Binto Thomas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources