Convert String to smalldatetime

Hi,

I am new to SSIS and have been trying to load a date from a Table1.ColumnA (varchar(50)) to a Table2.ColumnB(smalldatetime).

I tried using the Derived Column and Data Conversion controls but I get the below error.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

What do I do to load this data.

Thanks,

Noel

Noel Fernandez wrote:

Hi,

I am new to SSIS and have been trying to load a date from a Table1.ColumnA (varchar(50)) to a Table2.ColumnB(smalldatetime).

I tried using the Derived Column and Data Conversion controls but I get the below error.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

What do I do to load this data.

Thanks,

Noel

Try changing Table2.ColumnB to datetime rather than smalldatetime and see if it works.

-Jamie

|||

What type of database are you writing to? I can't replicate this against a SQL Server 2005 db.

If you can't change the datatype of the destination, you might try casting the column to a smalldatetime when you read it from the source.

|||

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

|||

Subhash512525 wrote:

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

Hi Subhash,

Well now I'm confused. First of all you said the error was:

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Then you said it was:

error while converting DB_STR type to DB_DATATIMESTAMP type

Which is it?

-Jamie

|||

Subhash512525 wrote:

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

Use this expression in a Derived Column transform:

Code Snippet

(DT_DBTIMESTAMP)(SUBSTRING(DateValue,1,4) + "-" + SUBSTRING(DateValue,5,2) + "-" + SUBSTRING(DateValue,7,2))

The date needs to have the dashes inserted for the cast to work.

|||

Jwelch,

Thanks for your support. That resolved our issue. I was unable to mark this as an answer, however I have marked that as a helpful post.

Regards

Subhash Subramanyam