Calendar date as string

[jamiedenman100]

Hi

Is there a way to use the calendar pop-up for a data input but have the input type (class) set to java.lang.string?

Our Oracle DB has nearly all it's "date" fields set as text, but I would like to be able to select a date from the calendar pop-up and pass parameter as type string to report (I use to_date in the report SQL).  When I try to send java date and use to_char I get errors on type mismatch.

Thanks in advance

[mdahlman]

First, I would like to commiserate with you. Using an Oracle database to store "dates" in varchar fields is a brutal abuse of the database. It will surely require ugly hacks like this in all of your applications. The database gods cannot be smiling.

In principle you could update the JavaScript in the calendar control to produce text instead of dates. This is probably difficult... but it's surely possible.

On the other hand, I don't see why there should be a problem with just using two parameters. MyDateParam would be a java.util.Date and MyStringParamHoldingADate would be a java.util.String. The default value of MyStringParamHoldingADate would be based on MyDateParam.

On the other hand (the third hand, I guess) you mentioned using TO_DATE in the report SQL. Are you performing TO_DATE on MyVarcharFieldHoldingADate and performing TO_DATE on MyStringParamHoldingADate to compare them? I might cry. You should be fine with just using a standard Date parameter and then writing your SQL query with something like this:

WHERE TO_DATE(MyVarcharFieldHoldingADate, 'yyyymmdd') <  MyDateParam

Regards,
Matt

[jamiedenman100]

Hi Matt, thanks for your reply

yes, tell me about it!!  It's due to this being a library management system and all the tables are created from MARC records so the dates are stored as text and in an alarming variety of formats!

I'll try and use the holding var within JS first as I've run into problems using this on the VARCHAR fields (librarians do not seem to care where the month is nor how the year is entered it seems)  I'll post an update with how this goes.

Thanks again

Jamie