|
| Re: convert datetime to date report parameter [message #901873 is a reply to message #901810] |
Tue, 14 August 2012 20:56   |
|
You could always do it in propertybinding tab of the dataset or convert
it in the beforeOpen script event of the dataset.
Jason
On 8/14/2012 12:07 PM, Mike Johnson wrote:
> 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 #903237 is a reply to message #903213] |
Wed, 22 August 2012 15:41 |
|
What I would do is drop the where clause from the sql editor and apply
it in a beforeOpen script of the dataset. Ie
select * from orderdetails could be in my sql editor and then in a
beforeOpen script
var dt = params["MyDateParameter"].value;
converteddate = convert dt; //convert to date time
this.queryText = thist.queryText + " where mydatetimefield =
"+converteddate;
You could also change the parameter to a datetime instead and just leave
the parameter in the query.
Jason
On 8/22/2012 10:08 AM, Mike Johnson wrote:
> I'm currently using the date report parameter (startDate) in my sql
> script to build the data " where assess_date = params["startDate"] ". I
> do it in the sql script to filter the number of records brought back by
> the dataset. Now that the lms requires a "datetime " format for the
> report parameter "startDate" I need to convert it from dateTime to Date,
> and then used the date value in my sql script. How do I reference the
> converted variable in my sql script?
|
|
|
|
|
| Re: convert datetime to date report parameter [message #903257 is a reply to message #903250] |
Wed, 22 August 2012 17:46 |
|
No I did not include one as you can do this a bunch of different ways.
You could call Java directly from the script like:
//assumes report parameter is a date
var pdt = params["myDate"].value;
var dts = new Packages.java.sql.Timestamp(pdt.getTime());
Does the db require Date, Time, or Timestamp?
Jason
On 8/22/2012 1:03 PM, Mike Johnson wrote:
> ... I'm assuming the "convert dt" the "convert" is a user defined function?
|
|
|
|
|
| Re: convert datetime to date report parameter [message #903585 is a reply to message #903280] |
Fri, 24 August 2012 12:25 |
 Mike Johnson
Messages: 11
Registered: March 2012 |
Junior Member |
|
|
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.02189 seconds
] 
Search
Help
Register
Login
Home
