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...

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