I was reading a post on the SSIS forum where someone was having trouble casting a string into a datetime value. The problem was that although the value in the string looked like a datetime value, it wasn't in a format that could be understood by SSIS.
That got me thinking, what values ARE valid values for SSIS to be able to cast as a datetime. So I knocked up a quick SSIS package to try and demonstrate. Its very simple as you can see here:
Simply I have a script component that puts some string values into the pipeline. The Derived Column component tries to cast them as DT_DBTIMESTAMP. Any that fail end up going down the error output. And I've got some data viewers to take a look at those that pass and those that fail. You can see those data viewers here below:
Straightaway you can see the values that passed and those that didn't so I won't waste time writing them out here again. If you want to have a go with this yourself with some values of your own then download the demo package from here.
-Jamie