jampabsatish (9/21/2012)

---

Hi ,

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 clx.aprvdt<>0

but when i join multiple columns to it give me an error

Msg 241, Level 16, State 1, Line 5

Conversion failed when converting date and/or time from character string.

select distinct

convert(datetime,CONVERT(nvarchar(27),clx.aprvdt)) as ADate

--,convert(datetime,cast(clx.aprvdt as varchar(30)),101) as ApprovalDate

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

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

--,ajm.ADJNUM

--,clx.CHECK#

--,clx.ACHK$

,clx.aprvdt

,'approved' as ApprovalStatus

,'default_data:1' as RequestingUserID

from CLMEXPM clx

join ADJMSTR ajm on ajm.ADJNUM=clx.ADJNUM

join CLMMSTR cm on cm.CLPOL#=clx.CLPOL#

join POLMSTR pl on cm.CLPOL#=pl.POLNBR

join CTS.dbo.CTS_CLAIM cts on cm.CLMNBR=cts.CLAIM_ID