Convert String to Date in DTS

[Lisa Vanderbilt]

I'm using SQL 7.0 SP1.

I am using DTS to read in a date with the format of YYYYMMDD. I am trying to convert this date to MM/DD/YYYY and then use the CDate function with the following code to load it into a Datetime column in SQL Server:

Function Main()
strDate = DTSSource("Col002&#34
strYear = Left(strDate, 4)
strMonth = Mid(strDate, 5, 2)
strDay = Right(strDate, 2)

strHoldDate = strMonth + "/" + strDay + "/" + strYear

DTSDestination("DateName&#34 = strHoldDate
Main = DTSTransformStat_OK
End Function

My DTS package will execute without errors, but it does not add the row. I have been successful using CDate when the source date is in the format MM/DD/YYYY.

Also, do you have any tips on how to debug DTS? How to see what's in a variable, etc.?

Thanks!
Lisa

[tammy]

i have run into having to convert dates in the same manner. your logic appears to be right, but the way you are referncing the source column is different than i have done in the past. how i do it is as follows:
good luck!


Function Main()

strYear = Left( DTSSource("Col002&#34, 4)
strMonth = Mid( DTSSource("Col002&#34, 5, 2)
strDay = Right( DTSSource("Col002&#34, 2)

strHoldDate = strMonth + "/" + strDay + "/" + strYear

DTSDestination("DateName&#34 = strHoldDate
Main = DTSTransformStat_OK
End Function


------------
Lisa Vanderbilt at 12/7/99 11:47:38 AM

I'm using SQL 7.0 SP1.

I am using DTS to read in a date with the format of YYYYMMDD. I am trying to convert this date to MM/DD/YYYY and then use the CDate function with the following code to load it into a Datetime column in SQL Server:

Function Main()
strDate = DTSSource("Col002&#34
strYear = Left(strDate, 4)
strMonth = Mid(strDate, 5, 2)
strDay = Right(strDate, 2)

strHoldDate = strMonth + "/" + strDay + "/" + strYear

DTSDestination("DateName&#34 = strHoldDate
Main = DTSTransformStat_OK
End Function

My DTS package will execute without errors, but it does not add the row. I have been successful using CDate when the source date is in the format MM/DD/YYYY.

Also, do you have any tips on how to debug DTS? How to see what's in a variable, etc.?

Thanks!
Lisa