Solved

Converting string to datetime

Posted on 2004-08-23
7
709 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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…
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…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

785 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