Thanks Luis for your reply,

Sample Data with decimal(18,0) datatype to be converted to datetime2

19970811

19970811

19970811

19970811

19970811

19970812

19970812

19970813

19970812

19970812

19970812

As the Datatype is in Decimal i convert into Varchar(27) as i need to convert to datetime2

Query :

select

convert(datetime2,CONVERT(varchar(27),clx.aprvdt),102) as ADate

from CLMEXPM clx where clx.aprvdt<>0

1997-08-11 00:00:00.0000000

1997-08-11 00:00:00.0000000

1997-08-11 00:00:00.0000000

1997-08-11 00:00:00.0000000

1997-08-11 00:00:00.0000000

1997-08-12 00:00:00.0000000

My questions is i run this query alone and works like a champ but when i add convert(datetime2,CONVERT(varchar(27),clx.aprvdt),102) as ADate in a large query joining multiple tables it throws an error.

select distinct

'CheckS' as SubType

,convert(datetime2,CONVERT(varchar(27),clx.aprvdt),102) as ADate

,clx.aprvdt

,'approved' as Approval

,'default:1' as UserID

from CLMEXPM clx

join tabA ajm on ajm.ADJNUM=clx.ADJNUM

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

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

where cm.STATUS='closed' and pl.FTYPE in(2,3)

and cm.CLMNBR>0

Msg 241, Level 16, State 1, Line 5

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

What is causing this error