String to date problem

[Arde]

Hello,

I am using ODBC (ADO) /SQL to connect to database. In my report I use the fields of our ERP-program. The problem is that I need the fields that are specified in the ERP-program as string, but the values are dates and there are some random text too. I only need the fields that are dates. I can't delete the texts in the program because the fields are locked.

I can convert the strings in to date format in my workstation, but when I publish the report on server and try to refresh the data, I get an error that the formula has wrong type values. On the report I use the Selection function Looks like ??.??.???? for the fields and it works fine on workstation side.

I tried to convert the string to date on the server side
but there I got an error:
"ADO Error code:0x
Source:Microsoft OLE DB Provider for SQL Server
Description:Syntax error converting character string to smalldatetime data type.
SQL State:22007"
I think this error is because there are text values too?

Does anyone have an idea how I can use only the fields that have date-values on them?

I am using CR 2008 and CR 2008 server.

Thanx in advance.

 

[Charliy]

if val(left({table.field},2)) in 1 to 12 and
val(mid({table.field},4,2)) in 1 to 31 and
val(right({table.field},4)) in 1900 to year(currentdate)
then <treat it like a date>
else <treat it like something else>

1900 was just arbitrary, pick a year you want to use as a minimum for validation - and if you're using European format just swap the second parts of the first two lines.