nomar2
asked on
Selection Formula with Date Range Crystal Reports
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 PM..it completely ignores the records in January 31st, if I make the second date Feb 01, so the date range is jan 01 <--> Feb 01..it 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.
it only returns records with a timestamp between Jan 01, 2009 12:00 AM <--> Jan30, 2009 11:59:59 PM..it completely ignores the records in January 31st, if I make the second date Feb 01, so the date range is jan 01 <--> Feb 01..it 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}))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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.tmASTimeE
Date ({vSchoolTotals3.tmASTimeE
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.tmASTimeE
{vSchoolTotals3.tmASTimeEn
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.
James
({vSchoolTotals3.tmASTimeE
Date ({vSchoolTotals3.tmASTimeE
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.tmASTimeE
{vSchoolTotals3.tmASTimeEn
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.
James