Convert DBs numeric date to SQL datetime

Hi,
I'm moving some data from AS400 DB2 to SQL Server and run
into date conversion problems.
My date from DB2 is numeric 8 characters eg "20031231".
How can I convert it to either datetime or smalldatetime
format in SQL server ?
If my table in SQL is defined as datetime/smalldatetime or
numeric, I will get error during the data pump process.
If I define in char format, data pump works fine but data
isn't the format I want ?
Can someone help ? Thanks.If the date is part of the PK for a fact or dim table, I would use an intege
r key and not datetime. INT or DECIMAL(9,0) should be sufficient for your ne
eds if that is the case. I sometimes import .txt files from AS/400 and the s
ource table must not allow
packed signs. In the activex script transforming the data from source to des
tination you could convert int to datetime or char to int to datetime if you
wish.
You might want to consider importing textfiles and not use the source table
directly since this option gives you a snapshot of the data. If the import f
ails you will always have a source file to check for errors. A transactional
source table might change
and the error be corrected without your knowledge.

convert datatype from db2 to sql

i am running a package which extracts result of count(x), count(y),sum(z) from db2(as400) servers

i want to store the result of all the three in a table to a column of datatype varchar(25).

can someone help me with the convert/cast function in db2 which i can include in the query.

something like : select cast(count(x) as varchar(25)) from lib.file

No, but we can help you use SSIS to convert them...

Have you searched IBM's site for a SQL reference document containing commands?|||

yaa Phil,even i thought it is better to use a data converter transformation.

the problem iam running into is iam trying to store int and decimal datatypes into the same output column.

which is a varchar.

the convertor works fine for int datatype but is giving error for decimal.

"datatype of output column 'colname' doesnot mach with the datatype 'system.decimal' of the source column 'columnname' "

"component DataReaderSource failed validation and returned validation status VS_NEEDSNEWMETADATA"

what i understand is it not problem with the conver transformation its problem with the data source reader.

let me be more clear i have 6 querys running agains AS400 in a loop 1,2,4,5 querys give output in int and querys 3,6 give output numeric.

when i put each of this querys(1,2,4,5) in datasource reader the output datasource reader pics DT_I4 as the result datatype and for querys 3,6 it pics DT_NUMERIC

i think i am left with option to change in the query itself

help needed.... thanks in advance