Prev: Merge 2 tables
Next: SELECT Issue
From: Kevin on 4 Dec 2009 09:04
You can also do this if you have no calendar table:

DECLARE @year char(4)
SET @year = 2007

SELECT cast(@year as datetime) + spt.Number
FROM master..spt_values spt
WHERE spt.Type='P' and year(cast(@year as datetime) + spt.Number) = @year

"Bob" wrote:

> If you don't have a calendar table you do this in SQL2005:
> DECLARE @year CHAR(4)
>
> SET @year = 2007
>
> ;WITH cte AS
> (
> SELECT 0 x, CAST( @year + '0101' AS DATETIME ) y
> UNION ALL
> SELECT x + 1, DATEADD( day, x, @year + '0101' )
> FROM cte
> WHERE x < 365 + CASE WHEN @year % 4 = 0 THEN 0 ELSE -1 END
> )
> SELECT x, y, DATENAME( weekday, x ) z, DATEPART( weekday, x ) a
> FROM cte
> OPTION ( MAXRECURSION 366 )
> GO
>
> I'll leave you to do the optional bit as I don't like Mondays ;p
>
>
 | 
Pages: 1
Prev: Merge 2 tables
Next: SELECT Issue