Hi! I have a problem extracting

[theamis]

Hi!

I have a problem extracting the right data where the date range will be the last year previous month of the current month.

Example 1:
My date is: 2/10/2003
I want to get all the data from 1/1/2002 to 1/31/2002 and not LastYearMTD.
Example 2:
My date is : 12/31/2002
I want to see all the data from 11/1/2001 to 11/30/2001.

By the way, I am using Crystal Report v7
Thanks for the help

 

[goranm]

Hello !

One way is to use the DateAdd function that came with version 8, but wich can be downloaded from

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/UFLDateAdd.exe.asp

Then you can create two formulas
@Firstdate
DateTime(year(dateadd("m",-13,currentdate)),month(dateadd("m",-13,currentdate)),1,0,0,0)

@Lastdate
DateTime(year(dateadd("m",-12,currentdate)),month(dateadd("m",-12,currentdate)),1,23,59,59)-1

Then in your Record Selection Criteria you write:
{Table.YourDate} >= @Firstdate and
{Table.YourDate} <= @Lastdate


This will pass the SQL to the database.

/Goran

 

[theamis]

I try the formula that you had given but it generate an error: &quot;A Date time is required here.&quot;

I always have a problem regarding the DateAdd function. Is there an alternative way to do this?

Thanks

 

[goranm]

Hi !

You can try to use Date instead of DateTime, like this:

@Firstdate
Date(year(dateadd(&quot;m&quot;,-13,currentdate)),month(dateadd(&quot;m&quot;,-13,currentdate)),1)

@Lastdate
Date(year(dateadd(&quot;m&quot;,-12,currentdate)),month(dateadd(&quot;m&quot;,-12,currentdate)),1)-1

/Goran

 

[goranm]

Hi again !

Another way without using the DateAdd function at all:

@Firstdate
if month(currentdate) = 1 then
Date(year(currentdate)-2,12,1)
else
Date(year(currentdate)-1,month(currentdate)-1,1)

@Lastdate
if month(currentdate) = 1 then
Date(year(currentdate)-2,12,31)
else
Date(year(currentdate)-1,month(currentdate),1)-1

/Goran

 

[theamis]

Hello!

Thank you very much for the replies. I'll definitely try it.