Please Help! Conversion invalid for datatypes when using pro_element

[balebob]

Hi All,

I try to pull the data from a Progress array (9.1E) over to a SQL 2000 table using PRO_ELEMENT and I recevied to following error:

"...Conversion invalid datatypes on column pair 1 (source column 'pro_element(item_date,1,1)'(DBTYPE_STR), destination column 'item_date##1'(DBTYPE_DBTIMESTAMP))."

If I change the datatype on the field in SQL table to varchar, then I can pull the data over. I have try to use SQL's CONVERT/CAST and still cannot convert the datatype at all.

Any advices or suggestions are greatly appreciated.

Thanks,
BoB.

 

[tamhas]

If you display what it is you get when you treat it like a varchar, what does it look like?

 

[balebob]

All I got is "?". The data-type on the Progress field is also date. That is why I do not understand why SQL think it is a string data type when pulling over the data (pro_element).

Thanks,
BoB.

 

[tamhas]

That ? is the Progress unknown value, corresponding to a SQL null. Are you sure that the field is full of valid dates? I would do a very simple query focusing on just that field and see what you get. Depending on the size of the array, you should even be able to bring back the whole array and look at it to see if it looks like you think it does.

In fact, come to think of it, PRO_ELEMENT() works on character strings, so start by doing a query to return the whole array and cast it to varchar. If it looks OK, then apply the PRO_ELEMENT() to that varchar. If that works, then cast that to date.