Query calculate 26 weeks - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Query calculate 26 weeks

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2012-02-28 : 21:15:43

Hi,

I have a parameter pass into a query, then how can the query calculate out 26 weeks automatically?

For example,

Declare @Date varchar(50)

SET @Date = '01-08-2011'

Then the query should auto select:

SELECT '01-08-2011','08-08-2011','15-08-2011' etc...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-28 : 21:47:04

[code]
declare @Date date -- DON'T USE VARCHAR for date !

SET @Date = '2011-08-01' -- Specify date in ISO format YYYY-MM-DD

-- use your own number tables if you have one
select dateadd(week, n.num, @Date)
from numbers n
where n.num <= 26

-- else use spt_values or F_TABLE_NUMBER_RANGE
select dateadd(week, n.number, @Date)
from master..spt_values n
where n.type = 'P'
and n.number <= 26
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-28 : 22:42:35

another way ... using recursive CTE


; with number as
(
	select 	num = 0
	union all
	select	num = num + 1
	from	number
	where	num <= 26
)
select	dateadd(week, n.num, @Date)
from	numbers n
where	n.num	<= 26

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources