I tried that, and it didn't change.
You are right, it is the time that is messing it up, but it didn't seem to matter if I put the zeroes in the Crystal prompt for or not. I played with different times and this did not seem to have effect - the existence of values, be they zeroes or valid times, was enough to mess it up.
I ended up fixing this with T-SQL, I figure it will be the best because it won't matter what version of Crystal, or the driver, or the client.
Here's the change to the WHERE clause in the stored procedure, where @p_clockin and @p_clockout are the two provided datetime parameters:
(labordtl.clockindate between cast(convert(char(11), @p_clockin, 113) as datetime) and cast(convert(char(11), @p_clockout, 113) as datetime
Thank you for everyone that offered help on the question. I appreciate it.
Anthony Hughes.
You are right, it is the time that is messing it up, but it didn't seem to matter if I put the zeroes in the Crystal prompt for or not. I played with different times and this did not seem to have effect - the existence of values, be they zeroes or valid times, was enough to mess it up.
I ended up fixing this with T-SQL, I figure it will be the best because it won't matter what version of Crystal, or the driver, or the client.
Here's the change to the WHERE clause in the stored procedure, where @p_clockin and @p_clockout are the two provided datetime parameters:
(labordtl.clockindate between cast(convert(char(11), @p_clockin, 113) as datetime) and cast(convert(char(11), @p_clockout, 113) as datetime
Thank you for everyone that offered help on the question. I appreciate it.
Anthony Hughes.
--- On Wed, 5/9/12, eugkd <eugkd@...> wrote:
From: eugkd <eugkd@...>
Subject: [Vantage] Re: Date ranges in SQL query for a crystal report
To: vantage@yahoogroups.com
Date: Wednesday, May 9, 2012, 8:49 PM
Â
Make sure you set the time part of the DATETIME parameters to all zeros when running the report.
--- In vantage@yahoogroups.com, Tony Hughes <thughes281@...> wrote:
>
> I just discovered something really frightening, and I'm trying to figure out if I'm doing something wrong.
>
> We are using Vantage 8.03.409a, with a SQL back end. All of our custom Crystal reports use a stored procedure.
>
> I wrote a stored procedure yesterday, essentially it queries labordtl with a date range.
> the where clause looks like this:
>
> "where labordtl.clockindate between @p_clockin and @p_clockout"
>
> I made a Crystal Report (Crystal 2008) with that stored procedure as its data source, I put the report in as a menu item in Vantage.
>
> If I run the report from within Vantage and give May 1 and May 8 as date parameters, as an example, I will get data back dated between May 1 and May 8.
>
> If I run same report from within Crystal Reports as installed on my desktop, give same parameters of May 1 and May 8 - I get data back dated May 2 to May 8.
>
> I tested a few other reports and it's same thing - any report that I run from my computer, and pulling data from a query that has in the where clause, "Date between DateParam1 and DateParam2" will NOT return data on the actual day of DateParam1.
>
> Can anyone help me understand where to look to solve this? Is it the Crystal run time?
>
[Non-text portions of this message have been removed]