Importing char field into datetime field

[DrewConn]

I am attempting to use a DTS job to import a csv file into a pre_existing table. The problem I have is the csv file has the dates stored as straight text with no /'s in them. (ie: 03062001) This is causing out of range errors on my import job.

What is the best way to get this type of data into a datetime field?

Thanks in advance.

 

[edge2000]

You can do fancy stuff in DTS but a straighforward way would be to bring the CSV data into a 'PreWash' table that consists of pure char fields where there is problem data, then add a process to the dts to execute a stored proceedure that uses convert to rewrite the problem field into a valid date format, then INSERT it into your final destination table.

I am sure you could do the CONVERT in the DTS but someone else may be able to help with that one.

Cheers Dave Dave
dab@completebs.com

http://www.completebs.com

See website for more info but we can develop most things for most people.

 

[tlbroadbent]

DTS Transformations are quite simple. You can use VB or Java scripting. I prefer VB scripting. Here is an example that formats a date for insertion into a SQL table.

'**********************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'**********************************************

Function Main()
Dim sDate

DTSDestination("PartNo&quot = DTSSource(1)
DTSDestination("Supplier&quot = DTSSource(2)

'transform date from mmddyyyy to yyyy-mm-dd format
sDate =
Right(DTSSource(3),4) & "-" &
Left(DTSSource(3),2) & "-" &
Mid(DTSSource(3),3,2)

If IsDate(sDate) Then
DTSDestination("PurDate&quot = sDate
Else
DTSDestination("PurDate&quot = Null
End If

DTSDestination("Quantity&quot = DTSSource(4)

Main = DTSTransformStat_OK
End Function

You can modify the transformations while creating the DTS package or after it has been saved you can open and edit it. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.