DTS Transformation-CDATE function 2

[prevost999]

I am attempting, through DTS, to bring delimited data from a text file into a SQL Server database. I am having a problem bringing over the dates(which appear in the source file as mmddyyyy(i.e. 092420001), and have tried the following syntax in an ActiveX Script Transformation:

Function Main()
DTSDestination("t_dtmd&quot = CDATE(DTSSource("Col056&quot)
Main = DTSTransformStat_OK
End Function

I tried everthing, but keep getting the following error:
Error Description: Type Mismatch: 'CDATE'

The datatype for the destination field is datetime.
Any help/advice out there?

The

 

[aalmeida]

REad the following Articles they may help you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_usage_3ayb.asp

AND:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_ax_06er.asp

Theyn have some considerations and examples on how to convert strings to Date using DTS AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[prevost999]

Thanks Al,
I read the details in both articles that refer to datatype
conversions and the CDate function, but am still not successful bringing over the dates.

Paul Prevost

 

[aalmeida]

did anything change? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[prevost999]

No, No matter what I try, I receive the same error.
(Type Mismatch: 'CDATE')

From my understanding, the CDATE function should take a text string from a source file and convert it into the proper OLE DB date format. The following is true for my scenario:

1. The source date is text and 8 chars. (i.e. 09242001)
2. The destination field datatype is datetime.
3. I am using the following syntax for my ActiveX script
transformation:
Function Main()
DTSDestination("t_dtmd&quot = CDATE(DTSSource("Col056&quot)
Main = DTSTransformStat_OK
End Function

I am really at a roadblock here.

 

[aalmeida]

Doe the DTS process any record from the text file? have you tryed to play with the format you have the date in the records to see if any format would process?
Have you tryed to use the Date Time transformation? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[prevost999]

Actually, acting on your suggestion, I just cut the text file down to 20-30 rows (from a few thousand), and the package executed successfully, so it looks like it may be a problem with the data at some point in the file. I will research more, thanks for your help!

 

[prevost999]

OK, Here is what I've found out so far.

The Cdate function seems to work when the text
is in the following format(s): 09/24/2001 or 09-24-2001,
but does not work when it just straight text 09242001

My next question becomes:
In the transformation script, how do I convert the
string 09242001 to 09/24/2001?

 

[MaxZak]

Try with the function

DateSerial( <<year>> , <<month>> , <<day>> ):

DTSDestination(&quot;t_dtmd&quot = DATESERIAL(right(DTSSource(&quot;Col056&quot,4), left(DTSSource(&quot;Col056&quot,2), mid(DTSSource(&quot;Col056&quot,3,2))

I've used it...it works!!!

 

[prevost999]

Max and Al,
Thanks for all your help, I am now able to bring over the dates. One more issue, however.

In the text file, sometimes the date value is 00000000, and the DateSerial function is bringing them over as 11/30/99. Any idea why, or how to retain the 0 value?

Paul Prevost

 

[aalmeida]

The 00000000 values will not go into the datetime columns because it will generate an &quot;out of range converting char to datetime error&quot; what you can do is make it NULL if the column allows it or find a valid date to replace it. AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;