Reverse Date Calculation

[tubbers]

I'm using Crystal Reports XI and connecting to an Oracle MDB. The database stores dates/times as a number and I've gotten a formula to convert those numbers into dates. However, when my users want to use a date as a search parameter, it really slows the report down. I've been trying to figure out how to have the users input a date as the search parameter and then behind the scenes convert it into the appropriate number so that I can query the database using the number.

This is the formula I am using:

DateTime(Date(DateAdd("d",val(left(x,5)),date(1800,1,1)))

So 7617025620 = 7/19/2008 0707

What I want to be able to do is have my user type in 7/19/2008 and have a formula to convert it to 76170. Is this possible?

 

[bdreed35]

Have you tried using this in your record selection formula?

Something like:

{table.field} = DateTime(Date(DateAdd("d",val(left({?DateParam},5)),date(1800,1,1)))

~Brian

 

[Turkbear]

Hi,
Try just using the input date (using a DATE type parameter)
then look at the SQL that is generated,,,It should pass to the database for Oracle to handle..

Is the TIME component actually relevant - if not, just use Date math ( Table_Date = (Input_Date - 1) ) for instance.

What connection type are you using to the Oracle database?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You could create a separate report (or a command) to populate a picklist for a number parameter, where you use the number for the value, and the date for the description. Then set "show description only" to true. Then your record selection formula would simply be:

{table.number} = {?Parm}

-LB

 

[Turkbear]

Hi,
Just another note: is the field actually defined as a DATE field in Oracle - If so, that 'number' is really Oracle's way of storing date time information. Passing an actual DATE to Oracle should perform well.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tubbers]

Hi, thanks for all the input.

1. I'm connecting via an ODBC connection
2. The field is stored in the Oracle database as a number
3. The time part of it isn't relevant in this particular report but it might be in other reports.

I finally figured it out on my own and came up with this:

{DATEFIELD} in  (DateDiff("d", Date(1800,1,1),{?Start Date})*100000) to (DateDiff("d", Date(1800,1,1),{?End Date})*100000)

I think this is very similar to what bdreed had offered as a solution. I end up with:

7617000000 = 7/19/2008 0000

 

[Turkbear]

Hi,
To improve performance, and insure complete compatibility with Oracle's abilities, try using the Oracle Server connection instead of ODBC ( Unless you are using the newest wire-protocol OBDC drivers, there is additional overhead when using ODBC and, in some cases, there are some Oracle features that may not be able to be accessed.)

BTW, the number Oracle uses to store DATEs is actually a hashed code for the date and time, not 'just' a number.
If it is defined in the table as a number field not a DATE type, speak to your DBA about why.






To Paraphrase:"The Help you get is proportional to the Help you give.."