Teradata database error when using a Universe-defined Date condition

[Former Member]

I have encountered an odd problem when connecting Crystal to a Universe. There is a pre-defined Date condition built into the Universe that is defined as follows:

@Select(Date\Week End Date) > @Select(Conditions\User Response Select Date week -28) AND @Select(Date\Week End Date) <= @Select(Conditions\User Response Select Date)

The filter is designed to retrieve a rolling 4 weeks of data.

When I try and create and refresh a Crystal Report that includes this Date prompt, I get the following error:

Failed to retrieve data from the database.

Details: 22003:(ODBC Teradata Driver)(Teradata Database) A character string failed conversion to a numeric value.

If I create the same query in a WebI report it works fine, but in Crystal the error is produced. I have also tried constructing the same prompt and filter within Crystal rather than use the pre-built prompt but that did not work either.

Does anyone have any experience or advice on this error? I can't seem to find many related postings on this specific error.

BO Enterprise XI 3.1 SP2 running on Windows 2008 Server R2

Crystal Reports 2008 SP2 running on Windows XP SP3

Universe is built on a Teradata 12 source

Thanks!

[Former Member · Accepted solution]

One item I found I had to do with Teradata was cast my date/time values to dates, otherwise I got weird errors like this. You say it's working in Web Intelligence, so this might not be the issue, but it is an easy thing to test.

In the universe, change your end date definition to

cast(table.end_date as date)

and see if that helps.

[Former Member · Accepted solution]

Thanks Dave. We gave it a shot but received the same error when using the cast function.

There are some documents on SDN that suggest the problem is related to the USER_INPUT_DATE_FORMAT parameter in the teradata.prm file, and we are just investigating that now.

[Former Member · Accepted solution]

What format has the Date been defined in your database? In Teradata.prm file the USER_INPUT_DATE_FORMAT by default is: yyyy-mm-dd. Either trying changing this parameter and match it as it is in TD or try casting your date object into this format. See what happens.

- Noman Jaffery

[former_member11696]

Moved to Universe Designer forum