?
Solved

string transformation to datetime

Posted on 2003-03-12
5
Medium Priority
?
466 Views
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:

03/12/2003,01:01:01
03/12/2003,02:02:02
03/12/2003,03:03:03

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?
0
Comment
Question by:bartonkj
[X]
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
5 Comments
 
LVL 1

Accepted Solution

by:
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.
0
 
LVL 3

Expert Comment

by:Frostbyte_Zero
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
Else
        Main = DTSTransformStat_Fail
End If

End Function
0
 

Expert Comment

by:CleanupPing
ID: 9275952
bartonkj:
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 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 12

Expert Comment

by:monosodiumg
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.

monosodiumg
EE Cleanup Volunteer
0

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