Dealing with the datetime and stored procedures

[lyanch]

Okay, I know that it will probably never happen that crystal reports will let you send only a date to a datetime parameter for a stored procedure. Now I need to find a way to deal with it. The specs are something like

Using only Crystal Reports and Stored procedures as necessary.

Input parameters:

DateStart, required, provide pop up calendar
TimeStart, optional, no format specified
DateEnd, required, provide pop up calendar
TimeEnd, optional, no format specified

I can't use strings because they want the pop up calander. The date field has to be sent so that the filtering takes place on the server (or let Crystal deal with 200,000+ records - which neither the dba nor I will not allow).

*If* I could set just the "time portion" of @dateStart parameter to default to the beginning of day I could just use the one parameter (they can change the time if they need to). Is there a way to do this?

Is there anyway to set any stored procedure parameter to a default of a formula that takes other parameters?

Thanks in advance

Lisa

 

[LMCRYER]

let me be sure I understand - your parameter is in the stored procedure, it is declared as datetime.

you want to set it in crystal to default to the beginning of the day (12:00:00 AM) correct?

lmc
cryerlisa@hotmail.com

 

[PCJock]

Why not pass it as a string? Use cast or convert to change the string to the smalltimedate type. If you want date and time separated then pass them separate as strings. Then concatenate the two strings together and convert to datetime. For example: set startdate= month + time. Use the startdate in your query.

I have also parsed the datetime string and passed it to the stored procedure as separate integers. Then used the dateserial function to convert the date integers to a date. Ex. set startdate = dateserial(Year,Month,Date). I can send an example stored procedure if you would like.

 

[lyanch]

lmc - Yes, the parameters are held in the stored procedure. Currently I have all four in the procedure defined as datetimes. I can change the two "time" parameters to strings or ints as needed.

PCJock - I cannot use strings for the dates as they require using the pop up calendar, which I can't use for strings.

Thanks

Lisa

 

[LMCRYER]

I do this all the time - and all I do is this:

parameters are set in stored procedure as datetime...

when you go into crystal, set the DEFAULT for those parameters as (whatever date you want) and set the time
to 12:00:00 AM.

when the user is prompted to input their selections, the default time will remain the same...(beginning of the day...)

they will be able to pick a DATE but the time should remain the default time..