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.

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?

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)

thanks adam

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.