Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Format date parameter for SQL query(Using scripting tab)
Format date parameter for SQL query [message #558606] Mon, 13 September 2010 12:32 Go to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 94
Registered: November 2009
Member
Hi,
I'm building a query using the scripting method. It will contain something along the lines of
this.queryText = "select * from table where date_col < " + getStartDate();


My report has a parameter and i use this to create the 12/12/2005. I've created a function to do this:

/* Function to get the first day of next month, minus one year */
function getStartDate() {
	var reportDate = params["ReportDate"].value;
	// Minus one from the date as BIRT returns 1 - 12 for month but Javascript Date constructor is zero based
	var firstOfThisMonth = new Date(BirtDateTime.year(reportDate), 
								BirtDateTime.month(reportDate) - 1, 1, 0, 0, 0, 0);
	var firstOfNextMonth = BirtDateTime.addMonth(firstOfThisMonth, 1);
	var startDate = BirtDateTime.addYear(firstOfNextMonth, -1);
	return startDate;
}


However when I print the query its of the form

select * from table where date_col <
Thu Oct 01 00:00:00 BST 2009


I can format it to be 01/10/2005 alright but i'm not sure how I know what is the correct format to use i.e. US or European. How does BIRT know this if I did the above as a parameter on a "normal" data set:

select * from table where date_col < ?


thanks
Re: Format date parameter for SQL query [message #558632 is a reply to message #558606] Mon, 13 September 2010 14:19 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Trevor,

can you try using a localized format?
importPackage( Packages.java.text );
var sdf = new SimpleDateFormat("MMM d, yyyy", reportContext.getLocale());
var mydtf1 = sdf.format( mydt1 );

Jason

On 9/13/2010 12:32 PM, TREVOR.HIGGINS@FINEOS.COM wrote:
> Hi, I'm building a query using the scripting method. It will contain
> something along the lines of
> this.queryText = "select * from table where date_col < " + getStartDate();
>
>
> My report has a parameter and i use this to create the 12/12/2005. I've
> created a function to do this:
>
> /* Function to get the first day of next month, minus one year */
> function getStartDate() {
> var reportDate = params["ReportDate"].value;
> // Minus one from the date as BIRT returns 1 - 12 for month but
> Javascript Date constructor is zero based
> var firstOfThisMonth = new Date(BirtDateTime.year(reportDate),
> BirtDateTime.month(reportDate) - 1, 1, 0, 0, 0, 0);
> var firstOfNextMonth = BirtDateTime.addMonth(firstOfThisMonth, 1);
> var startDate = BirtDateTime.addYear(firstOfNextMonth, -1);
> return startDate;
> }
>
>
> However when I print the query its of the form
>
> select * from table where date_col <
> Thu Oct 01 00:00:00 BST 2009
>
> I can format it to be 01/10/2005 alright but i'm not sure how I know
> what is the correct format to use i.e. US or European. How does BIRT
> know this if I did the above as a parameter on a "normal" data set:
>
>
> select * from table where date_col < ?
>
>
> thanks
Re: Format date parameter for SQL query [message #558645 is a reply to message #558606] Mon, 13 September 2010 14:57 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 94
Registered: November 2009
Member
I'll try that, but i'm not sure how it works.....where does the locale come from? Do I need to set something in the report? How does BIRT know what format to use when I use the ? format of building the query?

thanks

[Updated on: Mon, 13 September 2010 14:57]

Report message to a moderator

Re: Format date parameter for SQL query [message #558674 is a reply to message #558606] Mon, 13 September 2010 17:15 Go to previous message
Richard A. Polunsky is currently offline Richard A. Polunsky
Messages: 198
Registered: July 2009
Location: Houston TX
Senior Member

Trevor,

In order to avoid serious issues between the Java date object, JDBC, and your database, I have always passed the date parameter as a string.

(Note that BIRT does have a date parameter type, and it seems to work in some cases but not all.)

I then format my SQL query as "where mydate < to_date(" + params[mydate] + "). In your case you would use SimpleDateFormat to generate the formatted date string instead of bringing it through a user-typed parameter.

As to your locale issue, that's a matter for you to as the DBA of your specific database. (If the BIRT date parameter worked for you, I agree locale would not be an issue.)

Richard
Previous Topic:Optional Y Series Grouping
Next Topic:BIRT vs. Pentaho
Goto Forum:
  


Current Time: Sun Aug 31 04:38:15 EDT 2014

Powered by FUDForum. Page generated in 0.01641 seconds