| Format date parameter for SQL query [message #558606] |
Mon, 13 September 2010 16:32  |
 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 18:19  |
|
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 #558674 is a reply to message #558606] |
Mon, 13 September 2010 21:15 |
|
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
|
|
|
Powered by
FUDForum. Page generated in 0.03355 seconds
] 
Search
Help
Register
Login
Home
