Some Strange Date Formulas

[bradlee27514]

I'm sure this is possible, but can't seem to figure the coding out.

I have a report I want to run any given day and select data based on a start date and an end date.

I want the start date to be day 1 of 3 months ago.
I want the end date to be the last day of the last month.

For example,
If the report was run on April 26th
start date = 1/1/2010
end date = 3/31/10

 

[briangriffin]

What database are you using? The syntax may be slightly different for you, but for SQL Server you can use:

Select DateAdd(mm,-3,DateAdd(mm,DateDiff(m,0,GetDate()),0))

Select DateAdd(dd,-1,DateAdd(mm,DateDiff(m,0,GetDate()),0))

You would have to pass in the parameter date:

DateAdd(mm,-3,DateAdd(mm,DateDiff(m,0,{?ParameterDate}),0))

DateAdd(dd,-1,DateAdd(mm,DateDiff(m,0,{?ParameterDate}),0))

Ideally these dates would be evaluated and passed to the database server, and I typically use a command object to make it simple. But that should get you started.

 

[bradlee27514]

im not pulling the date from a db, i want to manipulate currentdate, basically want a formula for startdate and one for enddate, then just want to use them in the select expert

 

[bradlee27514]

oh wait, you are saying all of that would go in the select expert box right? my thinking was create a formula for start date and one for enddate and then use logic from there.

 

[fisheromacse]

I think these should work:

{@StartDate}
dateserial(year(currentdate),month(currentdate)-3,1)

{@enddate}
dateserial(year(currentdate),month(currentdate)-1,+1,1)-1

 

[bradlee27514]

thank ya'll so much

 

[briangriffin]

These formulas will work within Crystal:

StartDate:
dateadd("m",-3,date(2010,4,26) - Day ( date(2010,4,26) ) + 1)

EndDate:
DateAdd ('m',0,date(2010,4,26) - day(date(2010,4,26)) + 1)


Subsitute your parameter field for the "date(2010,4,26)" and put those fields in the report header just to visually verify that the correct dates are being returned.

Then you can remove them from your report and use the Select Expert to do something like

databasefield between StartDate and EndDate


The end date formula is actually midnight on the first of the following month. If your data is date only (not datetime) then you can remove the "+1" from the end of the formula.

 

[briangriffin]

Fisheromacse beat me to the punch! There are always multiple ways to accomplish the same thing with CR...