DateTime Param for a String field

[risman]

Hello,
I am using CR 8.5, SQL Server, OLE Db driver. I have a DateTime/String problem that I'm probably over thinking, but......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 to Dates, or DateTimes, (2 parameters: FromDate & ToDate). The application generating the Crystal Report wants to querie with the String format, but I'd like to make it easier for endusers to use a Date or DateTime parameter. Then they click on a calendar button or just enter say, 1/1/2006 & 1/20/2006. The helpdesk guy said to use DateValue to convert the field.
I've tried many combos between formulas and parameter types, but I return errors or blank reports. Do I set the parameters as dates or datetimes and convert them to strings in the record selection, or vice versa, or ? Oh, oh. I'm getting dizzy again. Thank you.

RIS

 

[GJParker]

If you have access to the SQL Server, then I would create a SQL server View to convert the string field to a date field

CONVERT(Datetime, Corrected_Date)

Once you have created the view then use this as your datasource.

If you don't have access to teh database then DateValue is the function to use. Please post what you have already tried with the results for further help.

Gary Parker
MIS Data Analyst
Manchester, England

 

[risman]

Hello,
I have access to the server, but I have never created a new view before.
I browsed this field using one of the dropdowns in the selection expert. It came back with this format:
"YYYY/MM/DD hh:mm:ss.xx"

Example 1:
Parameters {?FromDate} and {?ToDate} are set as DateTime

{MEDICAL_REPORT_OBJECT.WKT_ID} = 10 and
DateTimeValue ({v_MROCorrected.RSD_DATE}) >= {?FromDate} and
DateTimeValue ({v_MROCorrected.RSD_DATE}) <= {?ToDate}

Error In Crystal: Bad Date-time format string……………

Example #2
{?FromDate} and {?ToDate} are strings. I enter the parameters in the above mentioned format.

{v_MROCorrected.RSD_DATE} >= {?FromDate} and
{v_MROCorrected.RSD_DATE} <= {?ToDate} and
{MEDICAL_REPORT_OBJECT.WKT_ID} = 10
The selection expert says no errors found, but upon closing the expert….
Error: A Date-Time is required here - say ok - then run it and Crystal crashes with the “Send Microsoft An Error Message”…………


This report will run very nicely in Crystal using these:

{?FromDate} and {?ToDate} are Dates

Selection:
{@CDate} >= {?FromDate} and
{@CDate} <= {?ToDate} and
{MEDICAL_REPORT_OBJECT.WKT_ID} =10

{@CDate} formula =
Date(DTSToDateTime({v_MROCorrected.RSD_DATE}))

But if I put it into the Powerscribe Application, it won't work.
All of their other canned reports use the Date Ranges like:

( if({?DateRange} = 1) then
DTSToDate ({MEDICAL_REPORT_OBJECT.MRO_END_DICTATION})
in AllDatesFromToday
else if({?DateRange} = 2) then
DTSToDate ({MEDICAL_REPORT_OBJECT.MRO_END_DICTATION})in WeekToDateFromSun

etc....
Thank you,

Ris

 

[lbass]

Have you tried using:

DTStoDate({table.yourstringdate})

?? DTStoDate is a function available to CR.

-LB

 

[risman]

And use it in the selection querie like this:

DTStoDate({table.yourstringdate}) >= {?FromDate} and
DTStoDate({table.yourstringdate}) <= {?ToDate} and
{MEDICAL_REPORT_OBJECT.WKT_ID} =10
????????

Then would the parameters {?FromDate} and {?ToDate} be set as Date fields?

 

[lbass]

Yes.

-LB

 

[risman]

Hello,
Upon fixing the report as stated above, and closing the selection expert, I got the message: "A String is Required Here". I clicked "ok", and was then able to run the report without being forced back into the select expert or anything. The report ran great. I rechecked the Select Expert and Parameters, and they were still the way I had fixed them. I then clicked 'Show SQL Query'. Shouldn't there be a statement about the To and From date parameters? I did not see one. Anyway, I loaded it into the powerscribe application, but it would not work. The screen stayed gray when it should have displayed the report. Maybe I will try to create a new view as stated above. Is there a good place to find instructions for that?
Thank you,
RIS

 

[risman]

Hello,
Ok, I don't know if anyone will 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