Dates in Oracle SQL

[2004 IR Help]

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!