Dat format problem using Crystal Reports 8.5 & Oracle 8i

[cli]

Hi,

I am using Oracle 8i and crystal reports 8.5. I have several reports which are populated by procedures from on the database.

These procedures substring a date for use in if statements and concatenates it back together for a parameter. For some reason this works fine on my machine but not on another machine. It seems that the date format is yyyy-mm-dd on the other machine. I have checked nls_date_format and the regional date format settings and both appear to be dd/mm/yyyy. I have also tried putting alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss' in the packages but this also does not work.

Any ideas ?? I know that it is either a setting somewhere or esle there is possibly a work around within the package?

would REALLY appreciate any suggestions

cheers

 

[Naith]

When you say it doesn't work, what's the error message you get out of it?

From the solution you've tried so far, it seems like the symptoms have convinced you that this is an Oracle error.

There are a few things you might want to check in this respect.

First, bear in mind that NLS_DATE_FORMAT just changes the representation of the data. It doesn't change the data itself. That is to say, if your database stores your dates data as DD/MM/YYYY and your NLS_DATE_FORMAT is YYYY-MM-DD, then your data will be displayed according to the latter format, but the structure of the date data remains the same.

I don't know if you're on Oracle for Windows or unix, but the NLS_DATE_FORMAT in the init.ora file / HKEY_LOCAL_MACHINE\SOFTWARE (local client) is the first place I would look, too. If you're both on unix, check that your .cshrc/.profile both have the same TZ (TimeZone)variable.

Alternatively, you could just force the date format using the TO_CHAR function.

Naith

 

[cli]

Hi ,

Thanks for your suggestion. I am now not convinced it is an oracle setting but think that I am now closer to a soltion.


The error I am getting is ODBC error: [MERANT][ODBC ORacle 8 driver][Oracle 8]ORA-06550;line1,column8;
PLS--00306: wrong number or types of arguments in call to 'Pr_XXXXX'
ORA-06550:line1,column8;
PL/SQL: Statement ignored

The line it falls over on is when I try to substring the date using SUBSTR.


UPDATE TB_REPORT_PARAMS SET (CL_FIELD_VALUE) =
(SELECT '01'|| '/' || '01'|| '/' || SUBSTR(TB_RUN_DATE.CL_RUN_DATE,7,4) FROM TB_RUN_DATE)
WHERE
CL_REPORT_NAME = 'IMMRD00206MonthlyPortRefusalsReport'
AND CL_FIELD_NAME = 'CL_START_DATE';
END IF;

It doesn't fall over on my machine at all and substrings the date fine. But on a different machine it falls over because it reads the data back to front as fart as I can see.

Running on windows and have checked nls_date_format.

Have tried to char but still seems to fall over,
Am going to take another look at this option though.
But it is a bit weird that it works in one place and not in another on the same database which made me think it was a setting

any other ideas??

 

[Naith]

Execute

select SUBSTR(TB_RUN_DATE.CL_RUN_DATE,7,4) from TB_RUN_DATE;

on both your machines, and see if there's a difference in the return.

As far as your stored procedure is concerned, you might consider changing the block concerned so that it reads:


UPDATE TB_REPORT_PARAMS SET (CL_FIELD_VALUE) =
(SELECT '01'|| '/' || '01'|| '/' || TRUNC(TB_RUN_DATE.CL_RUN_DATE,'YYYY') FROM TB_RUN_DATE)
WHERE
CL_REPORT_NAME = 'IMMRD00206MonthlyPortRefusalsReport'
AND CL_FIELD_NAME = 'CL_START_DATE';
END IF;

Naith

 

[cli]

Hi,

When I select the date in both machines in a sql plus session it looks the same. If I execute the store procedure on both machines just from a sql pus session it runs fine and inserts correctly. If I call the store procedure from the crystal reports in the secong machine it falls over because it is trying to substring a date which is in a different format. It seems that the date format is altered by the crystal reports calling it (weird I know) , have checked all the settings in crystal reports and they seem fine. Also what is weird is that it is the same installation on both machines. Am going to try the trunc syntax now to see if that works. I do have a work around by using by dividing the store procedure in two and calling the first one from a bat file, then executing the report which calls the second. The first one will do all the work with the dates. Would really like to know though why crystal reports is changing the session date?

What do you think ?

Thanks for all the help btw!!

 

[lyanch]

A suggestion that may or may not work...

In Crystal Reports itself

go to

file -> options and change the default date format to something other than "default" on both machines (the same on each). Then go try the reports again.. who knows, it might work.

Lisa

 

[cli]

Hi,

Thanks for suggestion, have tried that and set the format
there but it still has the same problem ! It seems logical that it is something in Crystal Reports itself, do you know of any where else that you set default data types in crystal reports ?

Thanks,

Cli

 

[Naith]

All the TRUNC amendment does to your PL/SQL block is make it so that the date format is irrelevant. However, if the procedure runs successfully on both machines, then you should be alright with your substring.

If you have a date parameter on both machines, when you're prompted is it in the same format on both?

Naith

 

[synapsevampire]

Are the OS time settings the same for both systems?

If everything is identical in CR, and you're hitting the same database, then it sounds like either the above, or you might have some difference in the Oracle clients.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lyanch]

Could you show us the SQL that Crystal is sending in the two cases?

Go to database->show SQL to get it.

Lisa