Date (only) parameter for stored procedure

[caste11an]

I have a few reports that access stored procedures. However, we'd prefer to use a Date parameter and not a DateTime. Is there a way to do this in Crystal 10 or by modifying the stored procedure?

Thanks!

 

[synapsevampire]

Depends on the database.

Try posting technical information in the future (Crystal version,database used), it doesn't take much more of your time and not doing so is disrespectful of people's time here.

-k

 

[Maven4Champ]

caste11an,

I am dealing with the exact same issue at the moment. The field we are using has a TO_DATE on it and all we need is a DATE parameter passed to the Oracle Stored Procedure. The IN paramters are both DATE as well.

I tried defaulting in the parameter within Crystal to 12:00AM on both STARTDATE and ENDDATE but my VB app passing the values back to the report didn't take it well.

I would be glad to here any ideas on this as well.

 

[Turkbear]

Hi,
Please explain further what you mean by:

field we are using has a TO_DATE


If the IN variable is defined as a DATE type then no
TO_DATE function should be needed in your SP..



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Maven4Champ]

--PARAMETERS--
STARTDATE IN DATE
ENDDATE IN DATE
--------------

--WHERE CLAUSE--
WHERE TO_DATE(TABLE.DATE) >= STARTDATE AND TO_DATE(TABLE.DATE) <= ENDDATE
----------------

 

[Turkbear]

Hi,
By that I assume that the
TABLE.DATE field is actually a string..Why not pass the parameter(s) as a string then?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Maven4Champ]

I am doing a TO_DATE just because the field is a DATE TIME and I am converting to just DATE. Regardless, saying PARAMETER IN DATE should only require Crystal to pass a DATE parameter, not a DATETIME.

It seems like we are skirting over the real issue here.

 

[Maven4Champ]

I have figured a temproary workaround to comfort my users as it relates to data.

I am setting the default picklist value for the START DATE to 1/1/2007 12:00:00AM and the default picklist value for the END DATE to 12/31/2007 11:59:59PM and just advising them to change the dates and not times.

Ugh how frustrating!

 

[Turkbear]

Hi,
To convert a DateTime field to just Date it is simpler ( and probably a llttle faster) to use TRUNC, not To_DATE...
If TABLE.DATE = 12/31/2007 12:00 AM then

TRUNC(TABLE.DATE) would return 12/31/2007

- IIRC, when using a SP, Crystal will match the parameter type to the field it is being used as a criteria for, not how it is defined in the IN clause , hence DateTime.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Maven4Champ]

Thanks.

I tried this by editing my source view to TRUNC(TABLE.DATETIME) AS X_FUNDDATE

Then in my Stored Procedure I did:

STARTDATE IN DATE
ENDDATE IN DATE

WHERE X_FUNDDATE >= STARTDATE and X_FUNDDATE <= ENDDATE

I then verified database in Crystal report to catch the update of the stored procedure, yet it still prompts for datetime and I cannot change the parameters to anything else.

Is this just a bug with CR8.5?

 

[lbass]

If you are using 8.5, there is an option in file->report options to convert datetime to date.

-LB

 

[Maven4Champ]

Just tried that (again). I was aware of that feature but I thought it just converted dates displayed in reports as either datetime or date.

Regardless, it didn't change my prompt to date and not datetime.

Any other suggestions?