QUEREY A STRING FIELD WITH A DATE TYPE PARAMETER

[risman]

I am using CR 8.5, SQL Server, OLE Db driver. I have a Db field ({Corrected_Date}) that is a String. The format disregards leading zeros except for minutes and seconds.: 1/1/2006 2:09:07AM 10/2/2005 2:43:00PM etc.... I am searching for records corrected between two Dates, or DateTimes, (2 parameters: FromDate & ToDate). I've tried many variations and conversions of the parameters and the selection statement. This report will run on my desktop using Crystal Reports just fine. But when I try to load it into the PowerScribe application for others to run the report, it fails. Most of their canned reports use a date range. (1 for Today, 2 for Last Full Week, 3 for...etc.). Our Db guy is seeing this error, (I'm assuming in the event viewer of the server):

"java.io.FileNotFoundException:

http://cmdsmdicsql1:8080/webcore/crystalreport/crystalRptServer.xml

Any ideas or help would be great.

 

[synapsevampire]

Nice post, very comprehensive and well though out.

I would suggest creating a SQL Expression formula to convert it to a date.

So you'd likely use a CAST or CONVERT to do this.

I can't remember the syntax right now, and there may be another function within SQL Server for this (a quick post in the SQL Server forum will straighten this out).

So once the field is converted to a date, you can reference the SQL Expression field in the record selection and the work will be offloaded to the database.

-k

 

[risman]

Thanks Synapsevampire,
My issue isn't solved, but I think I'm heading right. A couple of thoughts..... 1) If I want the end user to be able to pick the FromDate & ToDate, (as DateTimes and not DateTime Strings), wouldn't I want to convert the DateTime Parameters to DateTime String Parameters? And then use them in the selection expert? 2) In 'options' under 'File', does the 'While Reading Records.....Convert Date Time Field To:' not come into play?
Thanks again,
Tom

 

[risman]

Hi Ken,
Ok, I don't know if you'll see this, but here is the solution:

The table I was hitting had strings for the data type. It's returns were formatted like this: Jan 1, 2006
But the application would not take a date or a string parameter in that form. (From what I could tell). So my
{?FromDate} and {?ToDate} had to be converted like so:
Date({?FromDate}) and Date({?ToDate}). Then I edited the parameters to default a string formatted like this: MM/DD/YYYY. It was that easy. I even asked the local Crystal trainers here, (from whom I have taken classes from), and it stumped them. They know what buttons do what using the Crystal Experts. But that's about it. I think teaching & understanding the SQL construction and fundamentals would be an extremely helpful precursor to learning Crystal Reports.
Again, thanks for your help.
Ris
(Oh yeah, and having vendors who actually support the products they sell. Or even using an up to date version that the 3rd party still supports.) <- soapbox