Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

579 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