First and Third Week of Every Month

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)

First and Third Week of Every Month

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2011-03-13 : 12:47:11

I've not had much luck here. Anyone have code snippet which will calculate the first and week week of every month for the current year??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-13 : 14:10:33

What consitutes your "first" and "third" week of every month?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-13 : 14:14:14

[code]SELECT DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) + m.num - 22800, 0) AS [Start of first week],
DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) + m.num - 22800, 6) AS [End of first week],
DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) + m.num - 22800, 21) AS [Start of third week],
DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) + m.num - 22800, 27) AS [End of third week]
FROM (
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11
) AS m(num)[/code]

N 56°04'39.26"
E 12°55'05.63"

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2011-03-13 : 17:47:54

Peso! You are the greatest!! Works like a charm!

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-03-13 : 22:12:15

quote:

Originally posted by Peso

SELECT	DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) + m.num - 22800, 0) AS [Start of first week],
	DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) + m.num - 22800, 6) AS [End of first week],
	DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) + m.num - 22800, 21) AS [Start of third week],
	DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) + m.num - 22800, 27) AS [End of third week]
FROM	(
		SELECT	 0 UNION ALL
		SELECT	 1 UNION ALL
		SELECT	 2 UNION ALL
		SELECT	 3 UNION ALL
		SELECT	 4 UNION ALL
		SELECT	 5 UNION ALL
		SELECT	 6 UNION ALL
		SELECT	 7 UNION ALL
		SELECT	 8 UNION ALL
		SELECT	 9 UNION ALL
		SELECT	10 UNION ALL
		SELECT	11
	) AS m(num)

N 56°04'39.26"
E 12°55'05.63"

Pesco,

I greatly admire the way you approach a problem, Could you enlighten me on what it all means. I just cannot get it

Eg: 12 * DATEPART(YEAR, GETDATE()) + m.num - 22800, --- what does 22800 represent etc

Thanks
Glen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-14 : 04:57:27

22800 are the number of months in 1900 years.

N 56°04'39.26"
E 12°55'05.63"

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-03-14 : 18:54:06

quote:
Originally posted by Peso

22800 are the number of months in 1900 years.

N 56°04'39.26"
E 12°55'05.63"

Thanks
Glen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources