SQL query puzzle

In 2005, 2008

declare @y char(4), @d varchar(20)
select @y = '2008', @d = 'Monday'

;with days as (
select 0 as d union all
select d + 1 from days where d < 370
)
select convert(varchar(10), dateadd(d, d, @y + '0101'), 101) from days
where year(dateadd(d, d, @y + '0101')) = @y
and datename(dw,dateadd(d, d, @y + '0101')) = @d
option (maxrecursion 400);

Mikhail

"sqlman" wrote in message

> Here is a puzzle-like task I am trying to solve:
>
> Get SQL SELECT statement which will give a list of all dates in a
> given year. Optionally: only include Mondays.
>
> Any ideas?
>
> Thanks!
 >> Stay informed about: SQL query puzzle 

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
 >> Stay informed about: SQL query puzzle 

On Wed, 26 Nov 2008 13:13:39 -0800 (PST), David Portas
wrote:

>On 26 Nov, 20:58, sqlman wrote:
>> Here is a puzzle-like task I am trying to solve:
>>
>> Get SQL SELECT statement which will give a list of all dates in a
>> given year.  Optionally: only include Mondays.
>>
>> Any ideas?
>>
>> Thanks!
>
>
>SELECT dt
>FROM Calendar
>WHERE dt >= '20080101' AND dt < '20090101';
>
>SELECT dt
>FROM Calendar
>WHERE dayofweek = 'Monday'
>AND dt >= '20080101' AND dt < '20090101';

Indeed, quite easy if you have a Calendar table. Thanks!

Although I'd prefer to use the following instead:

SELECT dt
FROM Calendar
WHERE Y = 2008

SELECT dt
FROM Calendar
WHERE dayofweek = 'Monday'
AND Y = 2008
 >> Stay informed about: SQL query puzzle 

If you're wondering where David's table called Calendar is coming from,
follow this link...
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-...auxilia
You can adapt the example table to your needs.

"sqlman" wrote in message

> Here is a puzzle-like task I am trying to solve:
>
> Get SQL SELECT statement which will give a list of all dates in a
> given year. Optionally: only include Mondays.
>
> Any ideas?
>
> Thanks!
 >> Stay informed about: SQL query puzzle 

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
>
>
 >> Stay informed about: SQL query puzzle