Crystal Reports 11 - Date Range Filter

Posted on 2013-01-03
Medium Priority
Last Modified: 2013-01-03
Hello experts,

I have a crystal report that is built on a Command that has a date field in the form of yyyymmdd.  I have desire to filter my data based on a user-entered date range, so to do that, I had to first have a formula that converts that field to a number:

@{Due Date}:
CDate (ToNumber (Left ({Command.apptDate},4 )),ToNumber (Mid ({Command.apptDate},5 ,2 )),ToNumber (Right ({Command.apptDate},2 )))

{?Due Date Param}
I built a parameter as a Date datatype, allowing range values, and assigned that parameter to the

Select Filter:
{@Due Date} = {?Due Date 2}

I now have a need to have the select the date equal to the range, but PLUS 30 days from the Due Date Variable above -- @{Due Date}

So, in theory something like:

Select Filter:
{@Due Date} = {?Due Date 2} + 30

So that if the data set looks like:


And you entered a range of

2012-11-25 to 2012-12-25

it would return dates in this range, up to 30 days in the future, or 2013-01-25:


I appreciate the time and help!

Question by:robthomas09
LVL 14

Accepted Solution

LinInDenver earned 1600 total points
ID: 38740551
In your report filter, you would do:

{@Due Date} in minimum({?Due Date 2}) to maximum({?Due Date 2}) + 30

or {?Due Date Param} instead of {?Due Date 2} - whichever one you are really using to get the date range from the user.
LVL 18

Expert Comment

ID: 38740900
Change your Command to include :

convert(datetime,<datefield>,112) as ConvertedDate
This will provide you with a field with date format
LVL 101

Assisted Solution

mlmcc earned 200 total points
ID: 38740906
Better would be to convert the parameters to the yyyymmdd format the dates are in since then the filter cabn be passed to the database.

{YourDateField} in Val(CStr(minimum({?Due Date 2}),"yyyymmdd")) to  Val(CStr(maximum({?Due Date 2}),"yyyymmdd"))

LVL 18

Assisted Solution

vasto earned 200 total points
ID: 38740970
If you convert the current data field to a real date you will be able to filter the data inside the command. There will be 3 advantages :
 1. Filter by date is faster then filter by string ( varchar, char ...)
 2. You will be able to filter the data on the server instead of downloading everything locally and filtering it inside Crystal reports. This will make your report much faster too.
 3. It is simpler,  the command will look like :

SELECT ....  convert(datetime,<datefield>,112) as ConvertedDate
FROM ...
WHERE convert(datetime,<datefield>,112) BETWEEN {?Date1} AND {?Date2}

Where {?Date1} AND {?Date2} are the parameters inserted by the user.

In 2008 you can use also date (instead of datetime) and your report will be even faster

Author Closing Comment

ID: 38741533
Thanks all!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question