How to use Date/Time Datatype

[hkjang]

Hi

I have a question how to use Date/Time Datatype on Jasperserver.

I would like to use period parameter, so I defined two parameter on iReport as below.

DATETIME_FROM : Class type is "java.sql.Timestamp"
DATETIME_TO : Class type is "java.sql.Timestamp"

On Sql )
to_char(convert_time(ss2s_log.event_gmt, 'GMT'), 'mm-dd-yyyy hh24:mi') between $P{DATETIME_FROM} and $P{DATETIME_TO}

When I defined new datatype as Data/Time, if I choose any date, it is expressed  like this 09-01-2008 17:07.
I expected the format is okey to the query, but when I run report I got below error message.

"Caused by: java.sql.SQLException: ORA-01843: not a valid month"
 

Would you please advice what I need to check?

Any advice will be appreciate.

Thanks,
HKJang

 

[andrewsok]

Oracle complains that you use non-date expression (formatted string) in "between .. and.. " condition.

Modify you SQL query that it uses plain reference to the field in the condition. You may still use formatting if it is required for the output.

Therefor , you SQL will look like :

 

SELECT ..., to_char(convert_time(ss2s_log.event_gmt, 'GMT'), 'mm-dd-yyyy hh24:mi'), ....

FROM ss2s_log

WHERE ss2s_log.event_gmt BETWEEN $P{DATETIME_FROM} AND $P{DATETIME_TO} ...

 

You use to_char(.) for the output, but use just ss2s_log.event_gmt for "between".

[hkjang]

Thanks for the answer,

It is work fine as I expected after change sql code.

Thanks,
HKJang