STARTS WITH and ENDS WITH help

[zb12]

I'm trying to convert a report from Impromptu into Cognos 8. The formula portions from Impromptu that I believe are causing the problems read ...DateFeesCollected like '12_2008'... I have tried to convert this into Cognos word for word and I get this error "An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'." I don't know what this means. I have also tried replacing "like" with "starts with '12' and ends with '2008'" and this didn't work either. The dates in this function are treated as text and that may be causing a problem as well. Are there any other functions I could try?

[blom0344]

Cognos 8 offers excellent examples when working with functions and operators.  ::)
sqlPrepareWithOptions indicates that the UDA can not compile a valid SQL expression.
try DateFeesCollected like '%12_2008%' (will only work for strings!!)

When working with dates try the wide range date functions, like:

extract(day,[somedate]) = 12 etc

[zb12]

Cognos 8 offers excellent examples when working with functions and operators.  ::)
sqlPrepareWithOptions indicates that the UDA can not compile a valid SQL expression.
try DateFeesCollected like '%12_2008%' (will only work for strings!!)

When working with dates try the wide range date functions, like:

extract(day,[somedate]) = 12 etc

Thats the problem, I don't believe that the system recognizes my date as a date, it sees it as a number or text.
I am new at this program and i was wondering what is a string? I usually run very simple lists :-)

[blom0344]

A string is text stored in a database as char, varchar,text etc. The point I am trying to make is that you need to have a clue about the datatype you are working with.

Cognos8 should recognize a true date and internally store it as a datetime.

When you say you are converting Inpromptu to Cognos8 what are you exactly doing? Cognos8 should be based on a Cognos8 Framework model, unless you are working with straight SQL .. (Please don't)

By the way, dates are not stored with underscores like 12_2008 , but this is perhaps the way Impromptu did work (no idea, never had the pleasure  ;D)

In case of doubt examine the way your data is stored. Perhaps this forces you in the proximity of a DBA, but those chaps are really not as bad as their reputation makes us believe :D

[zb12]

What I mean by converting is that we have reports that were run in Impromtu and now we want to only use Cognos. All I am doing is looking at the Impromptu reports and creating new reports in Cognos that look and run like the old ones.

The dates are underscored because the function "like" means that it will look for data that looks like '12_2008' where the underscore represents space in which a two digit day would occupy. Thats why I was asking if I need to use some other function such as "starts with" and "ends with" so that it would find the date range I'm looking for.

[blom0344]

Yes, I understand, but the functions you use depend on the datatype you encounter.
Datetime functions and string functions are seperate sets.

Converting reports is that much easier if you have a clue about the backend..

If it is a string , then - example - you could use 'starts with' or 'substring' or a whole bunch of other functions.

If it is a true date, then the extract function works like a charm.

The underscore MAY serve as a Wildcard , but perhaps Cognos sees it a an underscore.
You can quickly check by generating the SQL of the query and look at the native expression that is presented