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

0

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

alain_vu's picture
Joined: Oct 24 2006 - 9:29pm
Last seen: 4 years 5 months ago

3 Answers:

0

Hi,

Have you tried changing the type of the parameters startdat and einddat to java.sql.Date ?

Please try and let me know if any other error comes.

 

Thanks
Ankur Gupta


 

Ankur Gupta's picture
Joined: Jan 21 2013 - 10:36pm
Last seen: 11 months 1 week ago

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...

alain_vu - 5 years 3 months ago
0

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...

 

alain_vu's picture
Joined: Oct 24 2006 - 9:29pm
Last seen: 4 years 5 months ago
0

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/

Ankur Gupta's picture
Joined: Jan 21 2013 - 10:36pm
Last seen: 11 months 1 week ago
Feedback
randomness