string transformation to datetime

Posted on 2003-03-12
Medium Priority
Last Modified: 2008-02-01
I am trying to import information from a text file into a table using a DTS package created through the GUI.  I am experiencing difficulties transforming a string representing the time into a datetime field.  I have one column representing the date (format is MM/dd/yyyy - e.g., 03/12/2003) and that transforms with no problem.  I have another column representing the time in the format of HH:mm:ss (e.g., 13:15:03).  The original text file is tab delimited and has several columns but I have simplified things for testing purposes and have created a comma delimited file with just 2 columns:  date, and time.

The data in the text file looks like this:


The date converts fine.  When creating the transformation I run the test and it says it works fine;  however, when I execute the task, the time conversion gives me the following error:

The number of failing rows exceeds the maximum specified.
Insert error, column 2 ('time', DBTYPE_DBTIMESTAMP), status 6:  Data overflow.
Invalid character value for cast specification.

The destination table has both fields as type datetime.

How do you convert a simple time string into a datetime data type?
Question by:bartonkj
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

malekam earned 300 total points
ID: 8121729
A timestamp is both date and time.  You would be better off appending both columns into one field.

Expert Comment

ID: 8122692
Use an ActiveX Script:

'  Copy each source column to the destination column
Function Main()

Dim myDate
' Assumes Column 1 is Date and Column 2 is Time
myDate = DTSSource("Col001") & " " & DTSSource("Col002")

If IsDate(myDate) Then
     DTSDestination("DateField") = Cdate(myDate)
     Main = DTSTransformStat_OK
        Main = DTSTransformStat_Fail
End If

End Function

Expert Comment

ID: 9275952
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.
LVL 12

Expert Comment

ID: 11093051
No comment has been added to this question in more than 257 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: malekam http:#8121729

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question