[craziconkat]

Has any one ever encountered these error msg's before?

01SO1-Error in Row
22007-Invalid date, time, or datetime stamp
HY000-Expression Evaluation Error

I recieve these error msgs when I run a crystal 11 rpt off of 2 views I've created in Psql - version 8.5 off of a Paragon data structure. One view is in a main report, the other in a subreport - they're unliked reports.

The date fields are stored as utinyint for century, year, month, day. So, I've converted them into a date datatype in a view:

convert(
concat(concat(concat(concat(right(convert(convert( a.PostingDateC, sql_integer) + 100, sql_char),2),
right(convert(convert(a.PostingDateY, sql_integer) + 100, sql_char),2)),'-'),
concat(right(convert(convert(a.PostingDateM, sql_integer) + 100, sql_char),2),'-')),
right(convert(convert(a.PostingDateD, sql_integer) + 100, sql_char),2)), sql_date)

for posting date, maturity date, effective date, and trade date.

I've also included something in the WHERE clause to eliminate 'perceived' nulls:

a.postingdateC <> 0 and a.effectivedateC <> 0 and a.maturitydateC <> 0 and a.tradedateC <> 0

The thing is, these error msg's do not occur on a regular basis - only intermittently and if I run the select portion of the view in control center, I do not get any error msgs.

Any help or direction would be greatly appreciated!

Thanks!

Michelle

[mirtheil]

Cant' speak for the "Error in Row" or "Expression Evaluation Error" but the "Invalid date, time, or datetime stamp" error is fairly common. It can be caused when the date is not valid in terms of ODBC. A valid ODBC date has a month of 1 to 12, a day of 1 to 31 dependent on the month (can't have Feb. 30), and a year of 0000 to 9999.
You might try converting the date to a string, instead of a SQL_DATE, just to see if there's an invalid value being returned.
Restricting on the "C" portion of the date is a start but what happens if there's a century but no month or day? The restriction wouldn't stop it and an error would be returned.

[craziconkat]

That was it!!!

Eliminating C (century) on <>0 alone still brought back numbers that didn't correspond to a 'proper' date field (62 in month, 32 in day, etc). So, for every single C, Y, M, D fields (century, year, month, day) I included the following in the WHERE clause:

Century=20 (this system will be replaced by the time there is 22nd century data in it)
Year in 1 to 99
Month in 1 to 12
Day in 1 to 31

I know this won't handle the days of the month effectively (ie - 30 days in Feb, 31 days in Sept or leap year rules) but I'm hoping that there is some sort of front end control that forces the users to enter in dates correctly.

At least if I get this error message again, I know that there isn't. I'm not sure how I would code this in Psql anyway - I'm a newbie here.

I was able to handle all of this conversion in crystal but the performance of the report was too slow because I had to do record selection off of this date conversion. So, I had to switch to a view.

Thanks again for the help - I really appreciate it!

Michelle