CDATE in a DTS package MM/DD/YYYY BUG???

[kazaneh]

Hi there ,
I'm using a function in my DTS package to use the format MM/DD/YYYY for my DDBB. The problem is when I retrieve the colum from the flat file, sometimes works and it fails other times.

This is the function:
cDate(Mid(strDate, 5, 2) + "/" + Right(strDate, 2) + "/" + Left(strDate, 4) )

I send you an example:

If I test with 20060103, the result should be 01/03/2006. I get 03/01/2006

If I test with 20051226, the result should be 12/26/2005 and it works fine.

I tested with several dates but it desn't make sense to me.

Is there any bug??

Thanks a lot!!!

 

[unclejimbob]

Setting my regional settings to English(Australia) resulted in

1/03/2006
26/12/2005

which is as incorrect for me as for you because I expect to see 03/01/2006 and 26/12/2005

changing the regional settings to English (United States)

results in :

1/3/2006
and
12/26/2006

which is what you are after. I guess you either need to be in the US or perhaps avoid the whole cdate issue completely and use to a format that the OLE DB understands , i.e. yyy-dd-mm

Anyway for what its worth here is what I used to obtain the above results...

Function Main()
dim strDate
dim theDate
strDate = "20060103"
theDate = cDate(Mid(strDate, 5, 2) + "/" + Right(strDate, 2) + "/" + Left(strDate, 4) )

msgbox(theDate)

strDate = "20051226"
theDate = cDate(Mid(strDate, 5, 2) + "/" + Right(strDate, 2) + "/" + Left(strDate, 4) )

msgbox(theDate)


Main = DTSTaskExecResult_Success
End Function

Someone brighter than me can probably give you a better answer.

good luck

ujb