we just found a simple solution:
select * from user_objects where created > to_date( {?PM-Datum} ) - 1.
This produces something like this:
SELECT *
FROM user_objects
WHERE created > TO_DATE (TO_DATE ('2016-04-01', 'YYYY-MM-DD')) - 1.
We suppose this is a bug in the crystal reports java classes.
Your solution - INTERVAL '1' DAY results in:
ORA-30081: Ungültiger Datentyp für Datetime-/Interval-Arithmetik at oracle.jdbc.driver.
I think we can live with the "to_date" workaround.
Thanks and best regards
Joachim
*we* do not format the date with to_date(), the only thing we do is pass a java.sql.Date to Crystal. Crystal seems to make this to_date() with date parameters and oracle.
I will test tuesday with Oracle, but I tested with MSSQL and it I have this:
My query:
select * from myTable
where f_geb_datum < {?gebDatum}
order by f_geb_datum desc
The query I get when making a Crystal preview:
select * from myTable
where f_geb_datum < {d '2016-08-04'}
order by f_geb_datum desc
The query I get when using reportserver:
select * from myTable
where f_geb_datum < {d '2016-08-04'}
order by f_geb_datum desc
If I use "-", then:
My query:
select * from myTable
where f_geb_datum < {?gebDatum} - 365
order by f_geb_datum desc
In Crystal:
select * from myTable
where f_geb_datum < {d '2016-08-04'} - 365
order by f_geb_datum desc
In reportserver:
select * from myTable
where f_geb_datum < {d '2016-08-04'} - 365
order by f_geb_datum desc
Reportserver only passes a java.sql.Date to CrystalReports. CrystalReports seems to create here a {d '2016-08-04'} for MSSQL. I guess this is the same for Oracle (Crystal would create a to_date()): I will test this tuesday.
But, whatever the result, the basic idea is that *someone* has to format the expression. In the postings above, we recommended doing this by hand, as we always do in dynamic lists. Here, the situation seems to be a little different, since Crystal does this instead of doing it manually. But the idea is the same.
As you see in my examples, the "- 365" works with MSSQL, I suppose this should work with Oracle too. In your examples without "- 1", the formatting created by Crystal is correct in both cases. But something seems to be happening with the "- 1" expression. Can't you see what exactly arrives at Oracle with select * from user_objects where created > {?PM-Datum} - 1 ? Since Oracle says: "(ORA-00932: Inkonsistente Datentypen: DATE erwartet, NUMBER erhalten)", something must be arriving. Could you check with some profiler or similar ?
Could you try with the following (or similar) alternatives of "- 1" ?
1. select * from user_objects where created > {?PM-Datum} - INTERVAL '1' DAY
2. select * from user_objects where created > {?PM-Datum} - NUMTODSINTERVAL(1, 'day')
3. maybe there is some problem with the ordering: select * from user_objects where created > ( {?PM-Datum} - 1 )
4. the same for: select * from user_objects where created > ( {?PM-Datum} - INTERVAL '1' DAY ) and select * from user_objects where created > ( {?PM-Datum} - NUMTODSINTERVAL(1, 'day') )
I haven't tested this, but maybe something is happening with "- 1".
Best regards,
Eduardo
but you use the correct pattern when using the query without the "-1" as described below.
The datepicker has the format YYYY-MM-DD and this format is used in the to_date function when executing the report.
The only thing is: what happens with "-1"?
In crystalreports i see to_date(...) - 1 (which is a date) but i cannot see what the sql look like from reportserver.
Once again, the formatmask you apply seems totaly correct.
My guess: instead of TO_DATE ('2017-02-01', 'YYYY-MM-DD') - 1 there is somthing like TO_DATE ('2017-02-01' - 1, 'YYYY-MM-DD') which gives invalid number.
Best regards
Joachim
using dates is always a little tricky because so many participants have a preconceived opinion about the format pattern. Therefore i always control the format pattern myself. In doing so i can avoid implicit conversion incompatibilities based on a RDMS clients or a RDBMS servers or an OS or a VM's Opinion of the format pattern.
Thus said the problem here is to use the date picker that delivers a date and hand out a string to be pasted into the query that can be interpreted in the right way.
This works fine with dynamic lists and Japser Rpeorts and seems to be a little more tricky with chrystal.
In case your report works for you now without the -1 just add it on the other side of the inequation. pls. be aware that the compatibility of all the format masks is just accidential!
wbr jan
]]>The sql in crystal reports is: select * from user_objects where created > {?PM-Datum} - 1
without to_date().
When i execute the report in CrystalReportsDesigner the sessionbrowser (for oracle) shows this statement: SELECT * FROM user_objects WHERE created > TO_DATE ('01-01-2017', 'DD-MM-YYYY') - 1
When i execute a modified report without "-1" i see that ReportServer executes: SELECT * FROM user_objects WHERE created > TO_DATE ('2017-02-01', 'YYYY-MM-DD')
The dateformat ReportServer uses is YYYY-MM-DD (popup-calendar). There is no problem executing the query.
I cannot see what happens (in the sessionbrowser) with the query if i modify the query with the "-1" because i get an error.
I will not change the query with a to_date(), because the dateformat depends on the GUI and you know the format, see the working query.
Best Regards
Joachim
I don't quite understand: where does the TO_DATE() come from? You say from crystal not from you?
What happens if you change
select * from user_objects where created > {?PM-Datum}
to
SELECT * FROM user_objects WHERE created > TO_DATE ({?PM-Datum}, 'YYYY-MM-DD')
or similar?
What do you mean by "RS executes ? It gives me an error." ?
Regards,
Eduardo
you are right. I monitored the sessions.
The query in crystal was: select * from user_objects where created > {?PM-Datum}
CrystalReports executes: SELECT * FROM user_objects WHERE created > TO_DATE ('01-08-2017', 'DD-MM-YYYY')
RS executes: SELECT * FROM user_objects WHERE created > TO_DATE ('2017-02-01', 'YYYY-MM-DD')
The formatstring in the to_date function matches the format of the date.
The formatstring comes from crystal not from me.
The original (which gives the error)
CR executes: SELECT * FROM user_objects WHERE created > TO_DATE ('01-01-2017', 'DD-MM-YYYY') - 1
RS executes ? It gives me an error.
Regards
Joachim
{?PM-Datum} becomes a string when it is passed to the query before handing the query to the JDBC driver for execution.
So with:
to_date({?PM-Datum}, '?PM-Datums-format-mask ( PATTERN )') - 1
we create a date (in the database) and then we can handle date operations (in the database).
Regards,
Eduardo
correct, the parameter already has type=date, BUT you don't control the format you get. In some cases you may get
select *
from user_objects
where created > '20170130" - 1
in other cases:
select *
from user_objects
where created > '20173001" - 1
in other
select *
from user_objects
where created > '2017-30-01" - 1
so in order to control what you give to oracle you can convert it to a date *with the given pattern*:
select *
from user_objects
where created > to_date({?PM-Datum}, '?PM-Datums-format-mask ( PATTERN )') - 1.
Regards,
Eduardo
thanks for the answer.
I think there is no need to convert the parameter because it is of type date.
The report runs without modification in the designer and i think it will run in InfoView (CrystalReports-Server).
The construct in many of our reports with a date range is:
and start_dtm > {?pm_Start}
and start_dtm < trunc( {?pm_End}) +1 -- we want 23:59:59
I will look in the mentioned tables.
Best regards
Joachim
the problem here is an unknown format mask and an implicit conversion that fails. Consider your query:
select *
from user_objects
where created > {?PM-Datum} - 1.
Here oracle tries to implicitely convert {?PM-Datum}-1 into a date without exactly knowing what format mask ?PM-date might have. In our case try to find out ?PM-Datums format-mask by checking the log wth:
SELECT E.*, P.KEY_FIELD, P.VALUE
FROM RS_AUDIT_LOG_ENTRY E,
RS_AUDIT_LOG_PROPERTY P
WHERE
E.ENTITY_ID = P.LOG_ENTRY_ID
Filter column key_field for value "statement" and column value for "*PM-Datum*". It helps to restrict the datefield! Check the action statement_prepared and open_table_db_data_source. You find the parameter value at the very end of the string. Derive PM-Datum-format-mask and change your query:
select *
from user_objects
where created > to_date({?PM-Datum}, '?PM-Datums-format-mask') - 1.
Looking forward to hearing from you ...
Jan
]]>i found this error (ORA-00932: Inkonsistente Datentypen: DATE erwartet, NUMBER erhalten) in crystal reports using some date arithmethics.
Here is a simple example:
create a crystal report with this sql-command (i am using oracle):
select *
from user_objects
where created > {?PM-Datum} - 1.
where PM-Datum is of type date.
This caused the above error.
Best Regards
Joachim