I am trying to convert a string in the form of "ddmmmyy" to
a date in a DTS tranformation(activex script) in SQL Server. I have tried CDate and
Replace methods but to no luck.Any suggestion.

Thanks

I presume an example of your format is 15MAR05 as well as 01MAR05

To setup a test scenario I execute the following TSQL code
CREATE TABLE t1 (dateDDMMMYY char(7))

INSERT t1 VALUES ('15MAR05')
INSERT t1 VALUES ('01MAR05')
INSERT t1 VALUES ('15MAR99')
INSERT t1 VALUES ('01MAR99')

CREATE TABLE t2 (SQLDate DATETIME)

Then I created a DTS Package with Two Connections objects pointing to my the database where my source table is and then another where my destinationt able is. Then I setup a Transform Task

For the Transform between the two columns I select DATE Time String Transformation
In the Properties Source Date Format I put: ddMMMyy
In the Properties Dest Date Format I put: dddd, MMMM dd, yyyy hh:mm:ss.ffff tt

Then I ran it.
To Test I ran select * from t2

SQLDate
------------------------------------------------------
2005-03-15 00:00:00.000
2005-03-01 00:00:00.000
1999-03-15 00:00:00.000
1999-03-01 00:00:00.000


David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com