how to get first date from week number?

[Ichcha]

set datefirst 6
datepart(wk,'2011-01-12')

yields 2. I would like to derive 2011-01-08 from this number. please help.thanks in advance.

[Ichcha]

select CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, datecol), datecoll)), 101)

gives first date but how could i assign it as week1 in

sum(case when datepart(wk,datecol)=1 then count1 else 0 end)  week1,

am trying to put first date as column header.. ist possible?

[adam_k]

select CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, datecol), datecoll)), 101)

gives first date but how could i assign it as week1 in

sum(case when datepart(wk,datecol)=1 then count1 else 0 end)  week1,

am trying to put first date as column header.. ist possible?

Depending on the way you use to run this query, it is possible to use a variable as a column name.

Try inserting your query as a value of a variable and executing it with the
EXEC command.

Declare @week1 as varchar(10) 
Declare @SQL as varchar(1000) 
set @week1 = 'ABC'
set @SQL = 'select ColumnA as ' + @week1 + ' from tableA '

Exec(@SQL)

[Ichcha]

thanks adam