Solved

Converting string to datetime

Posted on 2004-08-23
7
717 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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 125 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 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