Jump to content
Changes to the Jaspersoft community edition download ×

Problem comparing a date parameter (java.util.Date) in an sql to a java.sql.Date field


alain_vu

Recommended Posts

in the WHERE- clause of my select I have:

         and
           (
            (
               $P{startdat} IS NULL and $P{einddat} IS NULL

            )
            or
               (
            NOT($P{startdat} IS NULL) and $P{einddat} IS NULL
            and account_period.date_start >= $P{startdat}
               )
            or
               (
                NOT($P{startdat} IS NULL) and NOT($P{einddat} IS NULL)
            and account_period.date_start >= $P{startdat}
            and account_period.date_stop <= $P{einddat}
               )
        )
 

account_period.date_start and _stop are of the type java.sql.Date

parameters startdat and einddat are of the type java.util.Date

executing the sql gives an error because these 2 types can't be compared, how should this situation be handled?

Many thanks in advance for any help.

Alain

Link to comment
Share on other sites

  • Replies 4
  • Created
  • Last Reply

Top Posters In This Topic

Hello,

I have tried changing the parameters to type java.sql.Date but that doesn't work either.

 

when parameter is java.util.Date, the errormessage is: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $5

when parameter is java.sql.Date, the errormessage is the same (the jasperstudio tool let's me select a date on a calendar but displays the result as '1/1/13' and '1/31/13' whilst I would expect something like '2013-1-1' and '2013-1-31' ?)

Thanks for helping me out here...

 

Link to comment
Share on other sites

Hello,
I have tried changing the parameters to type java.sql.Date but that doesn't work either.

when parameter is java.util.Date, the errormessage is: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $5
when parameter is java.sql.Date, the errormessage is the same (the jasperstudio tool let's me select a date on a calendar but displays the result as '1/1/13' and '1/31/13' whilst I would expect something like '2013-1-1' and '2013-1-31' ?)
Thanks for helping me out here...

 

Link to comment
Share on other sites

  • 3 weeks later...

Hi Alan,

Sorry for late reply, I think the problem is that postgre is not able to understand $P{} you need to use $P!{} , this tells the query executer that the content of teh parameter needs to be used in the query. 

Doing this you could force the date to be inclueded in the query 

 

And regarding the display result of the date, or for a particular format you want to compare ,you could place prameter for the startdate like $P{startdate_SQL} and define a default value of this parameter as 
new SimpleDateForrnat("yyyy-M-d").format(startdate)

Same kind of parameter can be made for $P{eindate} like $p{eindate_SQL} containing the default value expression as
new SimpleDateForrnat("yyyy-M-d").format(startdate)

and in the query you could use these two new parameters
$P!{startdate_SQL} and $P!{eindate_SQL}

 

Hope  that this helps

Thanks
Ankur Gupta
http://ankurthetechie.blogspot.in/

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...