Improve company productivity with a Business Account.Sign Up

x
?
Solved

Converting string to datetime

Posted on 2004-08-23
7
Medium Priority
?
746 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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

595 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