Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Converting string to datetime

Posted on 2004-08-23
7
Medium Priority
?
738 Views
Last Modified: 2008-02-01
i am importing data from a text file using dts.   i have fields with 8 digits that, during the import, need to be converted to datetime format.  for example, the data file has:
07142004 and during the import, i need to convert this to:
07/14/2004

I am using an ActiveX script:  

Dim thedatefield
dim inputcolumn
 
inputcolumn=CDate(dtssource("Col296"))

thedatefield=left(inputcolumn,2) + "/" + mid(inputcolumn,3,2) + "/" + right(inputcolumn,4)

if isdate(thedatefield) then
   dtsdestination("Effective_Date")=thedatefield
else
   dtsdestination("Effective_Date")=null
end if

and when I run it I get the error: Type mismatch: dtssource.

What is wrong here?

0
Comment
Question by:CTerreri
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 11873776
have you tried?

dtsdestination("Effective_Date")=CDate(thedatefield)
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 11873785
also, maybe try:

dtsdestination("Effective_Date")=Nothing
0
 

Author Comment

by:CTerreri
ID: 11873850
Still getting same error with both.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 17

Expert Comment

by:BillAn1
ID: 11873913
Cdate is expecting a string it can convert to a date
you should just have
inputcolumn=dtssource("Col296")
you are doing your own parsing to a date, you don't want to use cdate at this stage.

then you want to do cdate on the result

dtsdestination("Effective_Date")=cdate(thedatefield)
0
 

Author Comment

by:CTerreri
ID: 11873996
I'm still getting the same error with this code:

Dim thedatefield
dim inputcolumn
 
inputcolumn=dtssource("Col296")

thedatefield=left(inputcolumn,2) + "/" + mid(inputcolumn,3,2) + "/" + right(inputcolumn,4)

if isdate(thedatefield) then
   dtsdestination("Effective_Date")=Cdate(thedatefield)
else
   dtsdestination("Effective_Date")=null
end if
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
ID: 11874108
I just tested this exact script, with an input string of 08082004, and a destination col of datetime, and it ran fine  (I'm just mapping from col1 to col1)

Function Main()

Dim thedatefield
dim inputcolumn
 
inputcolumn=dtssource("Col1")

thedatefield=left(inputcolumn,2) + "/" + mid(inputcolumn,3,2) + "/" + right(inputcolumn,4)

if isdate(thedatefield) then
   dtsdestination("col1")=Cdate(thedatefield)
else
   dtsdestination("col1")=null
end if

      Main = DTSTransformStat_OK
End Function
0
 

Author Comment

by:CTerreri
ID: 11874620
I did not have this code within the Function Main and also did not have the statement  'Main = DTSTransformStat_OK' included.

Once I made these modifications the code ran fine.

Thanks for your help.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

722 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