SQL-server error - datetime and string to timestamp conversion [Solved]

[Errant]

Hello, I'm quite new to Cognos but have worked quite a lot with both SAS and a bit of SAP as well as native-SQL. I'm trying to construct a report were I concatenate a datetime (only date) and a string variable (only time) into a timestamp variable. I need both time as well as date and I need them in a date/time format so that I can in an easy way calculate the differences. All this works fine for Cognos but when this is sent to the database I get an conversion error. And I can for the life of me don't figure out what is wrong. I understand that it's a value in a row that does this, since the report works fine when I have a more restricted filter activated. And I think I know which created data-items that are the problem. So I need to catch these rows and do something different with them. I know this much, show I should be able to deal with it but I haven't found anything wrong yet.

My question, is there a way from Cognos to get the rows that causes these error? This kind of problem was always simple(relatively speaking) in SAS since the log usually prompted which rows that returned an error. And then I could change my logic to take care of these errors.

I have manually gone through the rows and the datetime values looks fine to me, the arithmetics I do should be ok:e. But of course, it's a couple of hundreds of rows so I might have missed something.

Or perhaps I'm doing something to complicated for Cognos to handle, and the error is kinda random? It might be this since i have gotten results sometimes when I have had the offending columns in the query.

Any pointers would be greatly appreciated but otherwise I'll have to try to walk along.

[Lynn]

If you post the specific error you are getting along with the syntax of your various expressions it might help. Are you certain of the datatypes for the data you are manipulating?

You didn't mention the type of data source you are using, but presumably it is relational?

If there is any way to have the database create the elements you need then it wouldn't be logic that is repeated in zillions of reports ad nauseum. Alternatively, consider having the modeler address it in the package.

[Errant]

Ah, of course. It's pretty generic (in my googeling tries at least, therefore I excluded it):

An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.

      Details 

UDA-SQL-0144 An arithmetic exception was detected. [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. UDA-CUR-0000 Unable to fetch the row. RSV-SRV-0042 Trace back: RSReportService.cpp(747): QFException: CCL_CAUGHT: RSReportService::process() RSReportServiceMethod.cpp(217): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_Request RSASyncExecutionThread.cpp(690): QFException: RSASyncExecutionThread::checkException 

Yeah, the database is relational and both the database model specs as well as Cognos says the var's are char (even If I would like to get more metadata from Cognos but to no avail).

Your last suggestion is perhaps what I think is the best solution and something I was aiming at. I might not be able to affect the creation of the elements in the database but if it's possible to model this in framework manager then I myself can do that.

Thx,
Errant

[Errant]

Doing this in framwork manager instead worked as a charm. The error-handling capabilities was better as well so I could find the logic that was missing.

Errant