ORA-01830-日期格式的图片结束...-但仅在where子句中-不在select中 [英] ORA-01830 - date format picture ends... - but only in where clause - not in select
问题描述
I created an easier to test post here: ORA-01847 day of month must be between 1 and last day of month - but data is OK Problem solution described there
我花了几个小时在网络上搜索类似的问题-最终我认为只有您才能帮助我.
It took me hours to search for a comparable issue in the web - finally i think only you out there can help me.
我通过SQL开发人员将查询发送到Oracle 11.
I send my Query to Oracle 11 via SQL developer.
您可能会忽略完整的子选择(它在pastebin中)-我添加了每一列的转储以查看详细信息.
You may ignore the complete subselect (its in pastebin) - i added a dump of each column to see details.
在where子句中比较时间戳时遇到了很大的问题:这是我的基本sql，最后一行没有where子句:
I got big Problems when comparing timestamps in where clause: here my basic sql without where clause in last line:
select prod,operating,fakeday,prod_origin,operating_origin,
dump(prod),dump(fakeday),dump(operating) from
(
http://pastebin.com/wqX7dJPA
) sub
-- works: where sub.operating < to_timestamp('20140101','YYYYMMDD')
-- works: where sub.fakeday < to_timestamp('20140101','YYYYMMDD')
-- works NOT: where sub.prod < to_timestamp('20140101','YYYYMMDD')
不带where子句的结果是
Result without where clause is:
'PROD'| 'OPERATING'| 'FAKEDAY'| 'PROD_ORIGIN'|'OPERATING_ORIGIN'|'DUMP(PROD)'| 'DUMP(FAKEDAY)'| 'DUMP(OPERATING)'
25.11.13 00:00:00,000000000|25.11.13 00:00:00,000000000|25.11.13 00:00:00,000000000|',20131125,'|'25.11.2013'| 'Typ=187 Len=20: 7,221,11,25,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,11,25,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,11,25,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
24.02.14 00:00:00,000000000|24.02.14 00:00:00,000000000|24.02.14 00:00:00,000000000|',20140224,'|'24.02.2014'| 'Typ=187 Len=20: 7,222,2,24,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,2,24,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,2,24,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|',20131028,'|'28.10.2013'| 'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|',20131028,'|'28.10.2013'| 'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
20.01.14 00:00:00,000000000|20.01.14 00:00:00,000000000|20.01.14 00:00:00,000000000|',20140120,'|'20.01.2014'| 'Typ=187 Len=20: 7,222,1,20,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,1,20,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,1,20,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
如果我在'prod'的最后一行过滤中添加where子句，我会遇到
If i add the where clause in last line filtering on 'prod' i'll run into
ORA-01830: Datumsformatstruktur endet vor Umwandlung der gesamten Eingabezeichenfolge
01830. 00000 - "date format picture ends before converting entire input string"
*Cause:
*Action:
如果我使用sub.operating或sub.fakeday的另一列，我将收到正确的过滤结果列表.
If i'm using the other column sub.operating or sub.fakeday i'll receive correct filtered resultlist.
where sub.operating < to_timestamp('20140101','YYYYMMDD')
另一个重要信息-我可以打电话
another important information - i can call
select prod,operating,prod-operating,fakeday
它将起作用->显示间隔操作的结果
and it will work -> shows a interval result for prod-operating
但是
where prod - operating = INTERVAL '0' DAY;
将导致:
ORA-01847: Tag des Monats muss zwischen 1 und letztem Tag des Monats liegen
01847. 00000 - "day of month must be between 1 and last day of month"
请帮助-我不知道.
推荐答案
更改会话集nls_date_format ='DD.MM.YYYY HH24:MI:SS';
alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';
这篇关于ORA-01830-日期格式的图片结束...-但仅在where子句中-不在select中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
