Skip to main content



      Home
Home » Archived » BIRT » convert datetime to date report parameter
convert datetime to date report parameter [message #901810] Tue, 14 August 2012 12:07 Go to next message
Eclipse UserFriend
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 16:56 Go to previous messageGo to next message
Eclipse UserFriend
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 10:08 Go to previous messageGo to next message
Eclipse UserFriend
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 11:41 Go to previous messageGo to next message
Eclipse UserFriend
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 13:02 Go to previous messageGo to next message
Eclipse UserFriend
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 13:03 Go to previous messageGo to next message
Eclipse UserFriend
... 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 13:46 Go to previous messageGo to next message
Eclipse UserFriend
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 14:14 Go to previous messageGo to next message
Eclipse UserFriend
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 15:46 Go to previous messageGo to next message
Eclipse UserFriend
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 08:25 Go to previous message
Eclipse UserFriend
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: Wed Jul 23 23:22:54 EDT 2025

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

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

Back to the top