I am transforming a date from text to smalldatetime,
however when I come across dates with years way in the
future, such as 3001, the transformation falls over with
this message

syntax error converting character string to smalldatetime
data type

I have tried using the advanced tab to throw out all rows
with dodgy dates into a text file but the task still falls
over.

Does anyone have any ideas of how I can get SQLServer to
accept these dates or how I can get them reported as an
exception and the rest of the job to run correctly?

Many thanks, Lynne

What is the Source?

If the source is capable then you could filter the Source rows by
placing a WHERE clause on the SourceSQLStatement

If the source is a text file then you can import to a staging SQL
Server table and maipulate from there.

SmallDateTime obviously only accepts dates up until and including the
year 2079. If these dates in your source are valid and expected then
the designer of the database has incorrectly constrained this attribute
to a smalldatetime when it should have been a datetime.




Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

Thanks for this. I am quite a novice and didn't realise
that dates only went up to 2079 using smalldatetime. It
is now working.

Thank you, Lynne