convert datetime to date report parameter [message #901810] |
Tue, 14 August 2012 12:07  |
Eclipse User |
|
|
|
Where and how can I convert a datetime report parameter to date format for use in my sql query? The LMS I'm using now requires that report date parameters be in datetime format. I want to convert the dateteime report format to date and use it in my sql query where clause.
When the LMS previously allowed date report parameters I was able to reference the date report in my sql query by reference the date report parameter. Now if I convert the datetime report parameter to date, how do I reference it in my sql dataset query, and where in the report processing stage do I convert the datetime report parameter, before the dataset or table that holds the data from query is initialized.
|
|
|
|
|
|
|
|
|
|
|
Re: convert datetime to date report parameter [message #903585 is a reply to message #903280] |
Fri, 24 August 2012 08:25  |
Eclipse User |
|
|
|
Good question... let me see if I can be a li'll more specific.
the report parameter is rpStartDte.
the new version on the lms require that date parameters be dateTime (birt) format as opposed to the old way date (birt) format.
-----------------------------------------------------------------------------
1. Example one works (report parametere is Date.
The old way using Date format report parameter works in my property binding, ex.
" where r2.chkStartDate = to_date('" + params["rpStartDte"] + "','mm/dd/yyyy') " +
---------------------------------------------------------------
2. This doesn't work, mainly because I don't know how to format map dateTime, ex.
report parametere is DateTime:
" where r2.chkStartDate = to_date('" + params["rpStartDte"] + "','mm/dd/yyyy hh:mi:ss') " +
-------------------------------------------------------------------------------
how can I get #2 to work? I'm getting datetime format errors.
I'm using BIRT 2.6.1 and Oracle 11g
Some of errors I'm getting.
java.sql.SQLException: ora-01841: (full) year must be between -4713 and +9999, and not be 0.
or if i change the datetime map, i'll get "invalid month".
Also, now that the report parameter must be datetime, the customers are still putting the date in as 6/19/2012, so I'm assuming the default if you don't put the time is something like "6/19/2012 12:00:00" or "6/19/2012 00:00:00".
Crazy huh? But like I said the code worked up until he had to dateime report parameter format instead of date. Help!!!!
I've attached some screen shots.
|
|
|
Powered by
FUDForum. Page generated in 0.24715 seconds