DTS DATE PROBLEMS YYYY-MM-DD

[6fingeredman]

Hi,

My Source table contains a varhcar(20) field which holds dates in the format YYYY-MM-DD, I am trying to convert the dates but am having problems. Ive attached 2 examples of how I have been trying. Please could someone give me some tips on where I am going wrong? I get the following error :'Type mismatch on CDATE' with both scripts

and : '
----------------------
Dim strDate
Dim dDate

Function Main()

strDate = DTSSource("clindate")
dDate = cDate(Mid(strDate, 9, 2) + "/" + Mid(strDate, 6, 2) + "/" + Left(strDate, 4) )

DTSDestination("Clinic_Date") = dDate

Main = DTSTransformStat_OK
End Function
--------------------------------------------

Dim dDate
Dim iDay
dim imonth
dim iyear

Function Main()


iday = cint(mid(DTSSource("clindate"),9,2))
imonth = cint(mid(DTSSource("clindate"),6,2))
iyear = cint(left(DTSSource("clindate"),4))

DTSDestination("Clinic_Date") = DateSerial (iyear, imonth, iday)

Main = DTSTransformStat_OK
End Function

 

[6fingeredman]

OK We totally simplified this but have one burning question, the code below works fine but if we use a 4 digit year it fails !

The error we get is data overflow invalid cast etc, we are SQL2000

So what I am saying is that if I change
iyear = right(DTSSource("clindate"),2)
to
iyear = right(DTSSource("clindate"),4)
it fails !

Dim iDay
Dim imonth
Dim iyear
Dim dDate


Function Main()
	iday = left(DTSSource("clindate"),2)
	imonth = mid(DTSSource("clindate"),4,2)
	iyear = right(DTSSource("clindate"),2)

	dDAte = (iMonth  + "/" +   iday + "/" + iYear)
 
    if isdate(dDate)<>True then
		Main = DTSTransformStat_SkipRow
	Else
		DTSDestination("Clinic_Date") =tDate
		Main = DTSTransformStat_OK
    end if
End Function

 

[DaveRolph]

I have exactly the same problem, I would love to know why it won't accept 4 digit years

 

[DaveRolph]

Surely someone knows a simple asnwer to this one ?