Passing date parameter to VB then to Oracle DB

[Maven4Champ]

Greetings,

Currently in my Crystal 8.5 report I have a date range prompt from ?StartDate to ?EndDate.

I am using Oracle SQL instead of MS SQL which was previously used with this VB application that distributes the reports.

In the old statement, you would say:
TABLE.COLUMN BETWEEN '|StartDate| 12:00:00 AM' AND '|EndDate| 11:59 PM'

This would work fine in MS SQL SERVER.

However, now the reports have been redeveloped and point to an Oracle database. We have two columns that the VB app utilizes:
1.) Formula String - Uses Crystal syntax to generate to CRVIEWER.
2.) SQL SELECT - Used for e-mail functionality and selects records, overwriting the SQL select statement used in the VB application.

So my problem is this. I am trying to pass the |StartDate| and |EndDate| to Oracle via SQL SELECT column and it's not taking it. It returns invalid month error even when using the TO_DATE on my TABLE.COLUMN field.

Any advice would be appreciated. Especially if you have been able to distribute reports with date parameters from Crystal/VB via Oracle Service to a client.

Thanks!

 

[synapsevampire]

Your question is regarding Oracle syntax within a VB app, which has nothing to do with Crystal, so you're best served to post in an Oracle forum

The function you want in Oracle is TO_DATE.

I don't have it available right now, but it's something like:

tabele.date > to_date(StartDate)-1
and
tabele.date < to_date(endDate)

Using greater than and less than eliminates the need to pass through times, which would have worked better in SQL Server as well as your old SQL Server statement would have disregarded 11:59:01 PM.

-k