Jump to content
Changes to the Jaspersoft community edition download ×

Dates in Oracle SQL

2004 IR Help

Recommended Posts

By: Todd - tberman333

Dates in Oracle SQL

2004-09-07 14:18

I am trying to use a date parameter in an SQL statement. The database is an Oracle database and there is no timstamp on the date fields. Basically, I need my date to be in the format of '07-SEP-04'. If I add that text to my SQL statement it works correctly, but I am unable to pass a parameter into the SQL statement and have it work correctly.

Can anyone tell me how I can create a parameter that would create a date in the formate above (also, what should the default value of that parameter be)?

Thank you in advance for any help!





By: Todd - tberman333

RE: Dates in Oracle SQL

2004-09-07 14:43

As an extra note on this... It would be OK if all the dates in my report were in the dd-MMM-yy format. If there is a way to set the "global" date formate in iReports, that would be perfect.





By: Rick Millar - rmillar

RE: Dates in Oracle SQL

2004-09-07 14:50

I'm sure there are several ways to skin the cat, but here is one way:


I define the parm (RPT_TO_PLAN_END_DT) as a text string and format the date in the string as "MM/DD/YYYY." The default expression can simply be the string "10/30/2004" for testing.


In my where clause where I reference the parm, i use a to_date function to convert the string to a date using the matching format mask.


where vtCmpuPlan3.VT_CMPU_PLAN_END_DT > to_date('$P!{RPT_TO_PLAN_END_DT}', 'MM/DD/YYYY')


I'm sure you can substitute the format mask you need to use. Take a look at the Oracle reference docs






By: Todd - tberman333

RE: Dates in Oracle SQL

2004-09-07 15:24

Thank you very much... That did work. If anyone knows a way to make the parameter a date (java.util.date or jave.sql.date) that would be very helpful... The only reason why we would want this is so users can choose their date from a calander in our scheduler rather than typing in the date as text (in order to do that, it needs to be defined in the report as a date).

Again... thanks for your help!

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...