Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » convert datetime to date report parameter
convert datetime to date report parameter [message #901810] Tue, 14 August 2012 16:07 Go to next message
Mike Johnson is currently offline Mike Johnson
Messages: 11
Registered: March 2012
Junior Member
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 #901873 is a reply to message #901810] Tue, 14 August 2012 20:56 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 #903213 is a reply to message #901873] Wed, 22 August 2012 14:08 Go to previous messageGo to next message
Mike Johnson is currently offline Mike Johnson
Messages: 11
Registered: March 2012
Junior Member
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 #903237 is a reply to message #903213] Wed, 22 August 2012 15:41 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 #903249 is a reply to message #903237] Wed, 22 August 2012 17:02 Go to previous messageGo to next message
Mike Johnson is currently offline Mike Johnson
Messages: 11
Registered: March 2012
Junior Member
Sorry but I gotta ask you this... how would you convert the datetime report parameter to date? Is there a way in BIRT? I've read that javascript doesn't handle datetime conversions very well.
Re: convert datetime to date report parameter [message #903250 is a reply to message #903249] Wed, 22 August 2012 17:03 Go to previous messageGo to next message
Mike Johnson is currently offline Mike Johnson
Messages: 11
Registered: March 2012
Junior Member
... I'm assuming the "convert dt" the "convert" is a user defined function?
Re: convert datetime to date report parameter [message #903257 is a reply to message #903250] Wed, 22 August 2012 17:46 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 #903261 is a reply to message #903257] Wed, 22 August 2012 18:14 Go to previous messageGo to next message
Mike Johnson is currently offline Mike Johnson
Messages: 11
Registered: March 2012
Junior Member
I need to convert datetime to date and datetime to string.
Re: convert datetime to date report parameter [message #903280 is a reply to message #903261] Wed, 22 August 2012 19:46 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Mike

Do you mean datetime birt parameter to sql date?

Jason

On 8/22/2012 2:14 PM, Mike Johnson wrote:
> I need to convert datetime to date and datetime to string.
Re: convert datetime to date report parameter [message #903585 is a reply to message #903280] Fri, 24 August 2012 12:25 Go to previous message
Mike Johnson is currently offline 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.






Previous Topic:How to keep date time FORMAT in column when exporting from server?
Next Topic:Bar Chart - Handling big variance of values
Goto Forum:
  


Current Time: Thu Oct 23 17:30:11 GMT 2014

Powered by FUDForum. Page generated in 0.02496 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software