Date Conversion throws truncation error

Hi,

I am trying to process a flat file feed. My date is in format of YYYYMMDD. The database column is of datatype "datetime". I have tried using all date related data types on FLAT FILE Connection Manager. I have also tried using Data Conversion Component. No luck so far!!

Any suggestion?

Thanks in advance,
-AnandTry the Derived Column Transformation.

This post doesn't solve exactly the same problem but its very very similar: http://blogs.conchango.com/jamiethomson/archive/2005/07/26/1867.aspx

-Jamie|||

The key point is, yyyymmdd cannot be casted to date type. (This has been bugged I believe, but feel free to log again it as I know I want it!). Using the Derived Column to crack it into format that can then be casted is the only route. I did test what was suppoted and what wasn't, but seem to have lost the packages for now....

I would also check you cracked format works for US/UK locales, as the flip-flop between the two can really mess you up. I like dd mmm yyyy myself, as no languages muck that up, not even VBScript! This may help as a start if you follow that format-

(MONTH(RowDate) == 1 ? "January" : MONTH(RowDate) == 2 ? "February" : MONTH(RowDate) == 3 ? "March" : MONTH(RowDate) == 4 ? "April" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "June" : MONTH(RowDate) == 7 ? "July" : MONTH(RowDate) == 8 ? "August" : MONTH(RowDate) == 9 ? "September" : MONTH(RowDate) == 10 ? "October" : MONTH(RowDate) == 11 ? "November" : MONTH(RowDate) == 12 ? "December" : "InvalidMonth")

|||Hi,

I have partial success with "Data Conversion". What should I do for the column which may contain '' or null?

One more thing: Is dt_dbdate in SSIS compatible with datetime in SQL Server 2005?

-Anand|||Hi,

Will this work in the "Derived Column"?

ISNULL([Maturity Date]) ? : (DT_DBDATE)(SUBSTRING(TRIM([Maturity Date]),5,2) + SUBSTRING(TRIM([Maturity Date]),6,2) + SUBSTRING(TRIM([Maturity Date]),1,4))|||I have resolved the issue using following "Derived Column" statement:

(TRIM([Maturity Date]) == "") ? NULL(DT_WSTR,8) : SUBSTRING(TRIM([Maturity Date]),5,2) + "/" + SUBSTRING(TRIM([Maturity Date]),7,2) + "/" + SUBSTRING(TRIM([Maturity Date]),1,4)

Cheers,
Anand