Parameter Date Format

Hi,
I am trying to pass a date paramter to the query, but no matter what format i set it to while setting it as a paramter, it is still passed to the query in yyyy-mm-dd format. Is there a way to change this?

Find more posts tagged with

Comments

mwilliams

Hi LetsBIRT,

What is the purpose for wanting to change the date format behind the scenes? Are you grabbing the parameter and putting it in the report?

LetsBIRT

I am going to filter on this date in the query.
Now, i do understand that i can change this to the desired format in the query using to_date(date,'mm/dd/yyyy') , but i was thinking that if i change the "Format as", in the "Parameter Page", that should be the way the date is passed backend.
If not, what is the purpose of this "Format as" for date?
I tried to test it on the iPortal to see if thats how it appears when we select a date from the calender, but No.
Let me know

Thanks...LB

mwilliams

LB,

I believe that the format is just for how the actual parameter is displayed. I'll have to look into it to make sure though. I'll post again if I figure anything further out.

cypherdj

Hi LetsBirt,

from my own experience, if you are planning to use Birt filtering, then the actual format is irrelevant. What happens is the parameter (params["MyDateParam"]) is a Date in yyyy-MM-dd format, but so are the date columns (dataSetRow["MyDateColumn"]) returned from the query so when you add a filter such as dataSetRow["MyDateColumn"] equalto params["MyDateParam"] both are in the same format (in any case, it's using Object.equals behind the scene and not a string comparison AFAIK).

In the same way, I haven't experienced any problems with using a Date parameter as a query parameter (as in the Parameters tab of the data set).

However, if you use the beforeOpen script to inject your filtering clause to queryText, then you need to add formatting.

Also, if you use DateTimeSpan.addDate or subDate to modify your parameter (eg. dataSetRow["MyDateColumn"] between params["MyDateParam"] and DateTimeSpan.addDate(params["MyDateParam"], 7, 0, 0), I find that I cannot preview the results due to conversion problems.

Hope that helps,
Cedric

LetsBIRT

Thanks for the information.

LetsBIRT

Is there a way to format the date in the expression builder of dynamic text and /or text Item?