alain_vu Posted January 9, 2014 Share Posted January 9, 2014 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.Dateparameters startdat and einddat are of the type java.util.Dateexecuting 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 More sharing options...
Ankur Gupta Posted January 9, 2014 Share Posted January 9, 2014 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. ThanksAnkur Gupta Link to comment Share on other sites More sharing options...
alain_vu Posted January 10, 2014 Author Share Posted January 10, 2014 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 More sharing options...
alain_vu Posted January 13, 2014 Author Share Posted January 13, 2014 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 $5when 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 More sharing options...
Ankur Gupta Posted February 1, 2014 Share Posted February 1, 2014 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 asnew 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 helpsThanksAnkur Guptahttp://ankurthetechie.blogspot.in/ Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now