问题描述
Crystal报表中是否存在错误,问题在于自oracle以来他无法恢复数据,我在oracle上进行了查询,但是当我将其发布到命令中时,起初它运行良好,但稍后向我显示此错误,请有人可以帮助我这是查询
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 meand 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
无法检索数据:
推荐答案
将日期和时间戳记不存储在适当的数据类型中,而是存储在数字中是一个不好的主意.这正好导致您面临的问题.
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.
您的错误在这里:
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
的类型为DATE
. TO_CHAR(...)
,但是是字符串.因此,Oracle会转换您的数据以便比较两者.它将您的字符串转换为DATE
,以便比较两个日期.您的字符串包含格式为"DD/MM/YY"的日期. Oracle会根据会话设置尝试以某种方式解释它.您收到错误,因为此操作失败.我想您的会话设置建议日期以月份开头,因此"13/02/17"被解释为"2017-13-02",其中包含无效的月份.
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.
要使用日期时间时不要使用字符串.为了获得时间戳记的日期部分,请改用TRUNC
.
Don't use strings when you want to work with datetimes. In order to get the date part of a timestamp, use TRUNC
instead.
查询已更正:
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;
如果您将时间戳记存储在DATE
中而不是自"1970-01-01 00:00:00"以来的秒数中,则查询甚至会更加简单.如前所述,不使用适当的数据类型(在这种情况下为DATE
,这是Oracle的datetime数据类型,即TIMESTAMP
)是个坏主意.
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
).
这篇关于Crystal报表无法从数据库检索数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!