Setting Month End date for parameter based on Start Date

[marydn]

Hello,

I have read all the questions and answeres for date parameters, but none seem to help me. What I would like to do, if possible, is update the enddate that is passed to my sp based on selection of start date.

For example, my user opens the report and is asked to supply a start date. She enters 12/01/2002. Now I want my end date to reflect 12/31/2002. My end user should not be allowed to enter an end date because that would create problems with report that is only intended to be a monthly report.

Any help would be greatly apprecited!!!

M.

 

[knzy]

dateserial(year({@StartDate}),month({@StartDate})+1,day({@StartDate})-1)

 

[DocFKNx]

This comes straight out of the CR 8.5 help. Of course FINDING it is a different issue - we all know how helpless CR's help is.

"Suppose you want to calculate the last day of the month for the DateTime field {Orders.Order Date}. Notice that in the calculation, DateSerial(Year(d), Month(d) + 1, 1) is the first day of the month after {Orders.Order Date} and then subtracting one day gives the desired result:

Rem Basic syntax
Dim d
d = {Orders.Order Date}
Formula = DateSerial(Year(d), Month(d) + 1, 1 - 1)


//Crystal syntax
Local DateTimeVar d := {Orders.Order Date};
DateSerial(Year(d), Month(d) + 1, 1 - 1)"

Previous poster had the same solution regardless.

X!

 

[rosemaryl]

Dear Marydn,

If I understood your post - you want this in your SP and not in Crystal. Since, you are passing the start date you can simply declare a variable and set the value for enddate as follows:

<Snip of sp>

CREATE PROCEDURE dbo.sp_DateReport_R
@startdate datetime
as
Begin
/*set @startdate = ('01/01/02')here for test - commented out for running from Crystal*/

declare @counter int
set @counter =1

declare @enddate datetime
set @enddate = DATEADD(mm, 1, @startdate)-1
/*right here this auto sets the enddate for use in your sp */

<end snip of sp>

Hope that helps,

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[marydn]

Well, intially I wanted to do it in the report. However, I ultimately decided to put it in the sp.

All the help I received, however, was excellent and I will use the functions in other reports.

Thanks to you all and Happy Holidays!