A client contacted us recently with a problem that was appearing when running a report.
The error would appear and no output would be created. The message was directly from SQL Server – “Conversion failed when converting date / time from character string”
Eagle-eyed readers may be able to see the cause of the problem. Most of the data items are wrapped in IsNull( ) statements with a ‘ ‘ string being used as the replacement value for NULL values.
IsNull([fieldName], ”)
The problem here is that even date fields are using the same syntax. SQL Server doesn’t know how to convert an empty string to a date or date/time field. Instead, the ” has to be replaced with a valid date field such as ‘2010-01-01’. I suppose you could also use ‘20100101’ to ensure SQL Server doesn’t get confused.
The same approach would be needed for numeric fields. You can’t do IsNull(NumericField,”) + 5. It may appear like you’re doing it but the result would be a string field that concatenates ” and the number 5.
The trick here is not to just change the Data Item Expression but also the Filter and Sort Expressions. The Sort Expression could likely be left as is, but the Filter expression is very important as that is what is used when prompting the user for ask at runtime values.
Here’s a video showing the results.
It’s hard for a non-developer to realize this mistake so it’s something that Foxfire! should identify and perhaps fix when the Data Dictionary is first identified. We’re looking at this for the first 2016 release.