Selection Formula with Date Range Crystal Reports

Posted on 2009-02-14
Last Modified: 2012-05-06
I pass in 2 dates ( January 01, January 31) in the form of parameters to a crystal report...the report works ..the problem is
it only returns records with a timestamp between Jan 01, 2009 12:00 AM <--> Jan30, 2009 11:59:59 completely ignores the records in January 31st, if I make the second date Feb 01, so the date range is jan 01 <--> Feb will then bring back the records in Jan 31st.

What changes do I need to make to the selection formula so that if I pass in Jan 1, Jan31 as the dates I will get the records for Jan31st..

Any help or insight would be much aprreciated.

({vSchoolTotals3.tmASTimeEnd} >= DateTime({?lReportDate1}) and

{vSchoolTotals3.tmASTimeEnd} <= DateTime({?lReportDate2}))

Open in new window

Question by:nomar2
    LVL 13

    Accepted Solution

    try date function instead of time as below:
    ({vSchoolTotals3.tmASTimeEnd} >= Date({?lReportDate1}) and
    {vSchoolTotals3.tmASTimeEnd} <= Date({?lReportDate2}))

    (date({vSchoolTotals3.tmASTimeEnd} ) in Date({?lReportDate1}) to Date({?lReportDate2}))

    LVL 2

    Expert Comment

    Your end date is defaulting to midnight of it's morning, so just declare the end date as a variable, add one day to the variable and bobs your uncle...
    LVL 34

    Expert Comment

    You could use Date() as crgary_tx suggested, although you would only need it on the second tmASTimeEnd.  Your parameters are already type date, and {?lReportDate1} will be converted to a datetime with a time of 00:00:00 (midnight), which is fine for the starting datetime.  So, you would only need to use Date when comparing with {?lReportDate2}, to keep the time in the field from excluding records.

    ({vSchoolTotals3.tmASTimeEnd}) >= {?lReportDate1} and
    Date ({vSchoolTotals3.tmASTimeEnd}) <= {?lReportDate2})

     However, the problem with using Date like that is that in my tests using an MS SQL datasource, if you use Date, that part of the record selection is not passed to the server, presumably because MS SQL has no date data type.  Using the formula above, the comparison with {?lReportDate1} would be passed to the server, but not the comparison with {?lReportDate2}.  So, every record with a date >= {?lReportDate1} would be sent from the server, and then CR would use the records with dates <= {?lReportDate2}, and filter out the rest.  If you're always going to be looking at recent data, then that may not be much of a factor, but if you try to look at some data from several years ago, for example, everything after {?lReportDate1} would be sent from the server.

     If you're using a different datasource (not MS SQL), then you may not have this problem.  You can check by going to Database > "Show SQL Query" and checking to see if the tests for both of those parameters are included.

     You can get around this problem by converting the ending parameter to a datetime, as you were doing, and manually setting the time for the converted value:

    ({vSchoolTotals3.tmASTimeEnd} >= DateTime({?lReportDate1}) and
    {vSchoolTotals3.tmASTimeEnd} <= DateTime({?lReportDate2}, CTime ("11:59:59 PM")))

     The generated datetime for {?lReportDate2} _will_ be passed to MS SQL server.  Interestingly, what actually happens is that CR replaces the test for <= your date at 11:59:59 PM with a test for < the next day at midnight.  For example, <= 02/16/09 11:59:59 PM is sent to the server as < 02/17/09 12:00:00 AM.  Weird.

     FWIW, you don't need to use DateTime on {?lReportDate1}.  It will be converted automatically.  You can leave it if you want.  I doubt that it makes any difference either way.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now