jampabsatish (9/21/2012)

I need to convert the varchar(30) to datetime2 in SQL server , which works well in a select statement

-select

--cast(convert(datetime2,cast(clx.aprvdt as varchar(10)),101)as datetime2) ApprovalDate

--from CLMEXPM clx where...

Try this

declare @Tmp table(PROPOSER varchar(100),APPROVER varchar(100),DESIGNATION varchar(100))

insert into @Tmp

select 'kiran','ARUN','CFO' union all

select 'kiran','kranthi','HOD' union all

select 'kiran','PHANI','CEO' union all

select 'kiran','SANGEET','ACCOUNT' union all

select 'SURYA','MALATHI','ACCOUNT' union all

select 'SURYA','SRIKANTH','ACCOUNT' union all

select...

you can use dynamic sql as

declare @Col_List varchar(10)=',QTR1'

Declare @sql nVarchar(max)='SELECT ACCOUNT'+@Col_List +' FROM TABLE_NAME'

EXEC @sql