Crystal Reports date parameter format vs. Oracle date format 1

[arock213]

Hello – long time reader, first time poster.

I am working in Crystal Reports v11 with an Oracle db, and trying to use a Command to create a report. To my knowledge, I need to use a command because I am performing a UNION query. The problem is that the user would like to use a Date parameter, and that the Crystal date format is not compatible with the Oracle date format. I have seen a bunch of posts on this issue, and researched for a week or more and cannot get anything to work. Here is the Command –

SELECT "TABLE_ONE"."REQUEST_ID", "TABLE_ONE"."REQUEST_DESCRIPTION",
"TABLE_ONE"."P_NASU_DT", "TABLE_ONE"."CUST_SLS_RGN",
"TABLE_ONE"."CUST_SLSTM", "TABLE_ONE"."CREATED_BY_USERNAME",
"TABLE_ONE"."P_ORG_ANALYST",
"TABLE_ONE"."CM_PROD",
"TABLE_ONE"."NUM_ACD"
FROM "DATABASE"."TABLE_ONE" "TABLE_ONE"
WHERE ("TABLE_ONE"."P_NASU_DT">='2008-01-01' AND
"TABLE_ONE"."P_NASU_DT"<'2008-03-01')
UNION
SELECT "TABLE_TWO"."REQUEST_ID", "TABLE_TWO"."REQUEST_DESCRIPTION",
"TABLE_TWO"."P_NASU_DT", "TABLE_TWO"."CUST_SLS_RGN",
"TABLE_TWO"."CUST_SLSTM", "TABLE_TWO"."CREATED_BY_USERNAME",
"TABLE_TWO"."P_ORG_ANALYST",
"TABLE_TWO"."CM_PROD",
"TABLE_TWO"."NUM_ACD"
FROM "DATABASE"."TABLE_TWO" "TABLE_TWO"
WHERE ("TABLE_TWO"."P_NASU_DT">='2008-01-01' AND
"TABLE_TWO"."P_NASU_DT"<'2008-03-01')

This command works successfully, but there are also 24 more UNION statements I eliminated to keep this post shorter, and the query takes over 20 minutes to run.

The field P_NASU_DT validates as True in the IsDate formula, but displays as string on the Crystal report.

Example of data in P_NASU_DT: 2008-01-04 14:25:46

I would like to replace all instances of yyyy-mm-dd in the above query with a parameter.

The most common error I get when playing around with this SELECT is the Oracle error “define not done before execute and fetch”, but I am not too familiar with Oracle and research has not been too fruitful.

Any help would be greatly appreciated!

 

[kskid]

Here's what I have done.

Go to Database -> Database expert. Right click on the command and click on edit and insert parameters. I use FrDate and ToDate.

From there, simply insert your parameters, [red]FrDate[/red] and [red]ToDate[/red] as date fields.

SELECT "TABLE_ONE"."REQUEST_ID", "TABLE_ONE"."REQUEST_DESCRIPTION",
"TABLE_ONE"."P_NASU_DT", "TABLE_ONE"."CUST_SLS_RGN",
"TABLE_ONE"."CUST_SLSTM", "TABLE_ONE"."CREATED_BY_USERNAME",
"TABLE_ONE"."P_ORG_ANALYST",
"TABLE_ONE"."CM_PROD",
"TABLE_ONE"."NUM_ACD"
FROM "DATABASE"."TABLE_ONE" "TABLE_ONE"
WHERE ("TABLE_ONE"."P_NASU_DT">={$FrDate} AND
"TABLE_ONE"."P_NASU_DT"<{?toDate}


When I run the report, I am prompted for dates

-lw

 

[arock213]

kskid - thanks for looking into this

the error I receive when using parameters as you describe is:

ORA-01861: literal does not match format string

I think the problem is that the Crystal parameter looks like 1/1/2008 and the Oracle database field looks like 2008-01-01, so they don't match up in the WHERE clause. if the Oracle field was in the 1/1/2008 format, your solution would work, but of course, nothing is easy...

I have tried reformatting the Oracle field in the query, and the Crystal field, and different combinations of each, but can't find the right code/statement.

 

[CrysRptDev]

Hi,

Try to paste your query in Oracle database to Add Command in Crystal Reports, then create parameters from there like kskid had mentioned. Make sure that you create parameters with DATE as datatype. I am sure your date field in your Oracle database with datatype is DATE right? If it is a date, then it will match up with the date you created for parameters.

Make sure your parameters and date fields in oracle match. If it is not working, try to use some function to convert date.
For example, to_char(date_field, 'Mon YYYY') or to_date(date_field, 'yyyy-mm-dd')

 

[kskid]

It shouldn't matter.

If "TABLE_ONE"."P_NASU_DT", ?FrDate, and ?ToDate are date values, then they should evaluate properly.

When you created the parameters in the Command edit/insert parameter screen, did you specify date as the data type?

 

[arock213]

Unfortunately I don't have access to the Oracle backend. Fortunately I did discover the "Show Field Type" option in Crystal, and it turns out that P_NASU_DT is in fact a String field.

I created String parameters (from and to dates) in Crystal, and modified the Select statement appropriately, and when prompted I typed in the dates exactly as they appear in the above SQL statement (2008-01-01), and it returned zero records.

Should that work, or am I missing something else?

 

[kskid]

If the table date field is a string, then change Your sql

WHERE to_date("TABLE_ONE"."P_NASU_DT",'YYYY-MM-DD HH24:MI:SS')>={$FrDate} AND
to_date("TABLE_ONE"."P_NASU_DT",'YYYY-MM-DD HH24:MI:ss')<{?toDate}

-lw

 

[arock213]

SUCCESS!

Thank you kskid! I played around with that to_date function for a while before, but could never get it to work. Your solution worked like a charm. Finally I can move on from this report...

thanks again!