Solved

Converting string to datetime

Posted on 2004-08-23
7
728 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP - How to insert data using sqlsrv on stored procedure? 8 57
Can we attach PDF to table 2 44
In T-SQL cursor convert smallint to varchar 15 50
Sql Query 29 30
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

737 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