How to use Parameter (Date with Time) in where clause

[thetnaung]

Hi.,

I'm trying to use parameter (which include Date and Time) in where clause as below in jasper report (jrxml)

My database is postgresql 9.1

$P{from_date} = "Tue Sep 02 08:00:00 ICT 2014"

$P{to_date} = "Wed Sep 03 08:00:00 ICT 2014"

in where clause, 

where transactions.movement_date >= ($P{from_date}::Date) AND transactions.movement_date < ($P{to_date}::Date) 

But this will only retrun the data from 2014-09-02 00:00:00 to 2014-09-02 23:59:59 

But what data actually i need is => from 2014-09-02 08:00:00 to 2014-09-03 07:59:59

Can someone please help and guide me the possible solutions?

 

Many Thanks.

TED

 

UPDATE, Parameter is JavaUtil::Date object which convert from string using java.util.Date

[thetnaung]

After some more research, I found the best way to do is to pass the data as string to Jasper and use $P!{} in where clause.

However, the value in parameter must include open and close single quote (' ') in order to be accepted by postgres (same as normal query).

$P{from_date} = " '2014-09-02 08:00' "

$P{to_date} = " '2014-09-03 08:00' "

and in where clause

where transactions.movement_date >= $P!{from_date} AND transactions.movement_date < $P!{to_date}