oracle ora-01841 full year 必须在 4713 和 9999 之间，而不是 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47713991/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ora-01841 full year must be between 4713 and 9999 and not be 0
提问by user2597012
I have been working on a query that is making me go crazy because I couldn't seem to understand the error message: my query is:
我一直在研究一个让我发疯的查询，因为我似乎无法理解错误消息：我的查询是：
SELECT MYTABLE." ID ",
NVL(max(TO_DATE(TO_CHAR(ADD_MONTHS(MYTABLE." XISSU_DT " ,MYTABLE." XTNR "), 'DD/MM/YYYY'),'DD/MM/YYYY')), TO_DATE(SYSDATE , 'DD/MM/YYYY') ) MAXLASTINSDATE,
TO_DATE(SYSDATE , 'DD/MM/YYYY'),
(TO_CHAR (TO_DATE(SYSDATE , 'DD/MM/YYYY')
- TO_DATE(NVL(max(TO_DATE(TO_CHAR(ADD_MONTHS(MYTABLE." XISSU_DT " ,MYTABLE." XTNR "), 'DD/MM/YYYY'),'DD/MM/YYYY')), TO_DATE(SYSDATE , 'DD/MM/YYYY') ) , 'DD/MM/YYYY')) * -1) MaturityPeriod
FROM MYTABLE
where
MYTABLE." STATUS " = 'A'
group by MYTABLE." ID "
the Error I have been getting is:
我得到的错误是：
ora-01841 full year must be between 4713 and 9999 and not be 0
Your help is really appreciated!
非常感谢您的帮助！
采纳答案by MT0
TO_DATE(TO_CHAR(datevalue, 'DD/MM/YYYY'),'DD/MM/YYYY')is removing any time component which is effectively the same as: TRUNC( datevalue ).
TO_DATE(TO_CHAR(datevalue, 'DD/MM/YYYY'),'DD/MM/YYYY')在除去任何时间组分，它是有效的一样：TRUNC( datevalue )。
Then TO_DATE(SYSDATE , 'DD/MM/YYYY')is probably where your error lies as TO_DATE( stringvalue, format_model )takes a string as the first argument so you are effectively doing:
然后TO_DATE(SYSDATE , 'DD/MM/YYYY')可能是您的错误所在，因为TO_DATE( stringvalue, format_model )将字符串作为第一个参数，因此您正在有效地执行以下操作：
TO_DATE(
TO_CHAR(
SYSDATE,
( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT' )
),
'DD/MM/YYYY'
)
It doesn't make sense as SYSDATEis already of the DATEdata type so you don't need to use TO_DATEwith it.
它没有意义，因为SYSDATE它已经是DATE数据类型，因此您不需要使用TO_DATE它。
Finally, TO_CHAR(SYSDATE - datevalue)*-1Why are you converting it to a string then multiplying it by a number when you can just do:
最后，TO_CHAR(SYSDATE - datevalue)*-1当您可以执行以下操作时，为什么要将其转换为字符串然后将其乘以数字：
(SYSDATE - datevalue)*-1
But you don't even need the *-1as you can just swap the terms around:
但你甚至不需要，*-1因为你可以交换条款：
(datevalue - SYSDATE)
Tidying it all up you want something like:
整理一下你想要的东西：
SELECT MYTABLE." ID ",
NVL(
MAX( TRUNC( ADD_MONTHS(MYTABLE." XISSU_DT " ,MYTABLE." XTNR ") ) ),
SYSDATE
) MAXLASTINSDATE,
SYSDATE,
( NVL(
MAX( TRUNC( ADD_MONTHS(MYTABLE." XISSU_DT " ,MYTABLE." XTNR ") ) ),
SYSDATE
)
- SYSDATE
) AS MaturityPeriod
FROM MYTABLE
where MYTABLE." STATUS " = 'A'
group by MYTABLE." ID "
