I am importing data from Access to SQL Server. There were two DateTime
fields in Access. One contained a date, the other a time created by the
TimeSerial serial function. The date imports correctly and the Time field
gets the error below. The field created on SQL Server is SMALLDATETIME for
both. Does the time value need to be casted and if so how is that done on
the DTS Transformations tab ?


Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:Invalid character value for cast specification.
Error Help File:
Error Help Context ID:0
1 2 |12|R B S B o s t o n |2 2 0 1 |R |S E G 1 |W F 0 5 0 9 0 7 9 9
1 2 |12|R B S B o s t o n |2 2 0 1 |R |S E G 1 |W F 0 5 0 9 0 7 9 9

Hello rmcompute,


This is because the date generated will be

1899-12-30 08:22:00.000

and this is outside of the ranges for a SMALLDATETIME datatype. It will
work with a DATETIME datatype.


This said. Do you not want to join the date and the time back together as
SQL Server has no concept of one without the other?


Here is some activeX trasnform script that will allow you to put your time
into the datetime column

DTSDestination("Name OF Your Destination Column") = CDATE( "1/1/1900 "
& DTSSource("Name Of Your Source Column"))




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Worked like a charm. Thanks for the help.

"Allan Mitchell" wrote: