Crystal report failed to retrieve data from databa

Question

Is there an error in crystal report the problem is that he can not recover the data since oracle, I made a query that is workable on oracle but when I post it on command, at first it works well but after a moment he shows me this error, please someone can help me and this is the query

select to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24') + (time)/60/60/24 
, 'YYYY-MM-DD') datestr,
L.LOGINID,
L.STATUS,
L.TIME,
O.PRESENTATION_NAME,

 N_CALLSANSWERED_
 from "REP_STAT_DB"."LOGIN"  L
           join "GCTI_DMART"."O_AGENT_DAY"  O on 
  L.AGENTDBID=O.CONFSERVER_OBJ_ID
           join "GCTI_DMART"."R_AGENT_DAY"  R on O.OBJECT_ID=R.OBJECT_ID
        join "GCTI_DMART"."T_AGENT_DAY" T on T.TIME_KEY=R.TIME_KEY AND 
    T.BEGIN_TIME=to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24') + 
  (time)/60/60/24 , 'DD/MM/YY')
    where   O.DELETE_TIME IS NULL

failed to retrieve data:

Answer 1

The error you get is ora-1843: not a valid month. This is a data conversion error which Oracle throws when we attempt to cast a string to a date and the string contains a value which is not a valid date.

As to why you don't get this when you run the query in Oracle it depends whether you're using the same database for both clients.

If you're running SAP against a different database that's simple: different database, different data sets. You just need to track down the rogue record (s).

If it's the same database in both cases that's harder. Maybe Crystal Reports sets a different session date format set compared to your Oracle client? This would bite if your query has implicit date conversions. There aren't any obvious candidates for implicit date conversions but you know your data model better than we do.

Answer 2

It is a bad idea to store dates and timestamps not in appropriate data types, but in numbers instead. Such leads exactly to the problems you are facing.

Your mistake is here:

T.BEGIN_TIME = to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24') +
               (time)/60/60/24 , 'DD/MM/YY')

T.BEGIN_TIME is of type DATE. TO_CHAR(...), however is a string. So Oracle converts your data in order to compare the two. It converts your string to DATE so as to compare two dates. Your string contains a date in the format 'DD/MM/YY'. Depending on session settings Oracle tries to interpret it somehow. You are getting the error because this fails. I suppose your session setting suggests that a date starts with the month, so '13/02/17' gets interpreted as '2017-13-02', which contains an invalid month.

Don't use strings when you want to work with datetimes. In order to get the date part of a timestamp, use TRUNC instead.

The query corrected:

select 
  to_char(date '1970-01-01' + interval '1' second * time, 'yyyy-mm-dd') datestr,
  l.loginid,
  l.status,
  l.time,
  o.presentation_name,
  n_callsanswered_
from rep_stat_db.login  l
join gcti_dmart.o_agent_day o on l.agentdbid = o.confserver_obj_id
join gcti_dmart.r_agent_day r on o.object_id = r.object_id
join gcti_dmart.t_agent_day t
       on t.time_key = r.time_key
       and t.begin_time = trunc(date '1970-01-01' + interval '1' second * time)
where o.delete_time is null;

If you stored the timestamp in a DATE instead of seconds since '1970-01-01 00:00:00', the query would even be simpler. As mentioned, it's a bad idea, not to use the appropriate datatype (DATE in this case, which is Oracle's datetime data type, or TIMESTAMP).