Specifying timein SQL

[yskripch]

Hello All,

I have been using JasperServer and iReport for a couple of months now and I have worked out most of the bugs I have with it. The last problem I cant figure out is how to specify the time in a date parameter. What I would like to do is be able to search a query based on dates and time.

Normally the following code would work

select *

from table

where date between to_date('' 11/18/2008 00:00:00','mm-dd-YYYY HH24:MI:SS') and  to_date('11/18/2008 23:59:59','mm-dd-YYYY HH24:MI:SS')

But when I have my user input set to use date datatypes, it passes the date but the time is always is set to midnight. I would like to be able to change the time to 11:59pm of the selected date. I do not want to use the date/time datatype because that would force the users to set the time themselves and our previous reporting system did not require that. Does anyone know how this can be done? The only way I have thought of is to use a scriplet to change the time after the user selects the date in the parameter so that I could just use the parameters in the query. But if there is an easier way of doing this please let me know. Thanks a lot.

[yskripch]

There are two ways to solve this problem.

 

1st:

 

Have the input control use the date/time datatype which will allow the users to select the date and time. You would have to set the parameter class to Timestamp.

 

2nd:

 

Have the users select only the date and then modify that parameter to include the correct timestamp. The tricky part is that you must use the java.sql.Timestamp class because it holds on to the timestamp. If you use the java.util.Date class then the timestamp will be truncated when the Date class is changed to java.sql.Date class.

 

Hope this helps.