Eugene Elutin (9/17/2012)
a bit shorter version...
DECLARE @year int
SET @year = 2015
SELECT
MONTH(mfd) AS MonthNo
,DATENAME(MONTH,mfd) AS MonthName
,DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, mfd))) / 7 -
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, mfd)) / 7 AS NoOfSaturdays
FROM (
SELECT CAST(CAST(@year * 100 + m.m AS CHAR(6)) + '01' AS datetime) mfd
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m(m)
) q
ORDER BY q.mfd
I like it.
I'd move the math for generating day-1 of the desired year into a variable at the top of the script, just for readability, but it works as-is.
DECLARE @Year CHAR(4) = '2012'; -- input parameter if proc
DECLARE @StartDate DATE = @Year + '0101';
SELECT DATEADD(MONTH, [month], @StartDate) AS MonthStart
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) AS TVC([month])
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon