Date parameters and mysql not giving correct results.

[john.v.little]

If I use the following SQL direclty against my DB to query for a range of dates (not times, just the dates):

 

select count(p.id) as registrations

from playeraccount p

where date(p.creationTime) >= current_date 

  and date(p.creationTime) <= current_date 

  

it returns say 1, i.e there was 1 registration today. 

 

Now I turn this into a jasper report. 

 

I add the following SQL:

 

select count(p.id) as registrations 

from playeraccount p 

where date(p.creationTime) >= $P{from} and date(p.creationTime) <= $P{to}

 

And in my report, I create 2 new parameters of type java.util.date, and set the expression for both to "new Date()".

 

When I run the report, the result I get is 18, not 1! 

 

18 is not the total registrations (which is about 2000), nor is it the number of registrations since yesterday (which is about 10), it is seemingly a random number.

 

The problem could be java dates also include the time, I only want the date part. 

 

I also tried this: 

 

select count(p.id) as registrations 

from playeraccount p 

where date(p.creationTime) >= date($P{from}) and date(p.creationTime) <= date($P{to})

 

etc. 

 

ALL return incorrect values. 

 

1) is there any way to see what parameters are actually being sent to the DB (which I have no admin access to)? 

2) any other ideas how to restrict to a date (not time) range? I have tried all the examples I can find, and they are all wrong - i.e are not filtering between the two desired dates, but between some other values which are different. I also tried making the parameters prompt, and selecting todays dates on the calendar widget, but this gives the same garbage result.

[john.v.little]

I also tried 

select count(p.id) as registrations 

from playeraccount p 

where date(p.creationTime) BETWEEN $P{from} AND $P{to}

 

and 

 

select count(p.id) as registrations 

from playeraccount p 

where p.creationTime BETWEEN $P{from} AND $P{to}

Etc.  all return the same incorrect result.

[john.v.little]

I tried today() instead of now() - same result. Its producing date for approx the last 7 days, not today.