Concatenate a string problem

Hi to all:

In my package, I have in OLE DB SOURCE a statement:

DECLARE @.CMonth as smalldatetime

SET @.CMonth = '11/1/2006'

select day(@.CMonth)+month(@.CMonth)+year(@.CMonth)as ID_Month

and in OLE DB DESTINATION, I have ID_Month column as a char(10).

I want to be result as concatenated string – 1112006, but I receive 2018 (As a result of calculation)

Can anybody help me? Thank you

DECLARE @.CMonth as smalldatetime

SET @.CMonth = '11/1/2006'

select cast(day(@.CMonth) as varchar(2))+cast(month(@.CMonth) as varchar(2))+cast(year(@.CMonth) as varchar(4)) as ID_Month

|||Thank you very much Attila Soos. It works great!