August 18, 2011 at 5:40 am
Hi,
Does anyone know how to compile a string of week No's between 2 days but using iso_week as it doesn't work with dateadd function:
I have it working between 2 dates for wk number but need iso_week to work
declare @startdate as datetime, @endDate as datetime
select @startdate = '20110102', @endDate = '20110104'
declare @DatelistCol varchar(max), @castDatelistCol varchar(max)
set @DatelistCol = isnull(@DatelistCol + ',','')
set @castDatelistCol = isnull(@castDatelistCol + ',','')
select @DatelistCol = @DatelistCol + substring(
(select ',' + quotename('wk '+cast(datepart(ww, dateadd(ww, t.n-1,@startdate)) as varchar(12)), '[')
from dbo.Tally t where dateadd(ww, t.n-1, @startdate) <= @endDate
for xml path('')),2,200000)
select @DatelistCol
Just noticed this doesn't work either as if i use @startdate = '20110804', @endDate = '20110808'
then i only get one week where as it crosses 2 weeks ... any help would be greatly appreciated.
August 18, 2011 at 6:15 am
This article on SSC should assist you
http://www.sqlservercentral.com/articles/Advanced+Querying/onthetrailoftheisoweek/1675/
Be sure to read the comments on the article
August 18, 2011 at 6:39 am
worked it out ... in case anyone else ever needs it:
declare @startdate as datetime, @endDate as datetime
select @startdate = '20110101', @endDate = '20110119'
declare @DatelistCol varchar(max), @DatelistCol2 varchar(max), @DatelistCol3 varchar(max)
;with cte
as
(
select datepart(iso_week, dateadd(d, t.n-1, @startdate)) col1, t.n, row_number() OVER (PARTITION BY datepart(iso_week, dateadd(d, t.n-1, @startdate)) ORDER BY t.n ) as r
from dbo.Tally t where t.n-1+@startdate <= @endDate
)
--select * from cte
select @DatelistCol3 = substring(
(select ',' + quotename(col1, '[')
from cte where r= 1 order by n for xml path('')),2,200000)
select @DatelistCol3
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply