SQL to_date ora-01847 月份的日期必须介于 1 和月份的最后一天之间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15277203/
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
to_date ora-01847 day of month must be between 1 and last day of month
提问by rocky
I am trying to execute the below query but getting this error:
我正在尝试执行以下查询，但收到此错误：
to_date ora-01847 day of month must be between 1 and last day of month
to_date ora-01847 day of month must be between 1 and last day of month
query is to check overlaping of date and time in two different tables
查询是检查两个不同表中日期和时间的重叠
table1 (emp_num1 number,start_date1 date,start_time1 varchar2,end_date1 date,end_time2 varchar2)
table1 (emp_num1 number,start_date1 date,start_time1 varchar2,end_date1 date,end_time2 varchar2)
table2(emp_num2 number,start_date2 date,start_time2 varchar2,end_date2 date,end_time2 varchar2)
table2(emp_num2 number,start_date2 date,start_time2 varchar2,end_date2 date,end_time2 varchar2)
select *
from table1 t1
,table2 t2
where t1.emp_num 1 = t2.emp_num2
and to_timestamp(to_char(start_date1,'DD-MON-YYYY')||' '||NVL(start_time1,'00:00'),'DD-MON-YYYY HH24:MI')
between
to_timestamp(to_char(start_date2,'DD-MON-YYYY')||' '||NVL(start_time2,'00:00'),'DD-MON-YYYY HH24:MI')
and
to_timestamp(to_char(end_date2,'DD-MON-YYYY')||' '||NVL(end_time2,'00:00'),'DD-MON-YYYY HH24:MI')
or
to_timestamp(to_char(end_date1,'DD-MON-YYYY')||' '||NVL(end_time1,'00:00'),'DD-MON-YYYY HH24:MI')
between
to_timestamp(to_char(start_date2,'DD-MON-YYYY')||' '||NVL(start_time2,'00:00'),'DD-MON-YYYY HH24:MI')
and
to_timestamp(to_char(end_date2,'DD-MON-YYYY')||' '||NVL(end_time2,'00:00'),'DD-MON-YYYY HH24:MI')
the above query resulting the error:
上述查询导致错误：
to_date ora-01847 day of month must be between 1 and last day of month
to_date ora-01847 day of month must be between 1 and last day of month
I tried running query
我尝试运行查询
select to_timestamp(to_char(start_date1,'DD-MON-YYYY')||' '||NVL(start_time1,'00:00'),'DD-MON-YYYY HH24:MI') from table1
no error is encountered.
没有遇到错误。
回答by álvaro González
To reproduce the error:
要重现错误：
SELECT NVL(SYSDATE,'00:00')
FROM DUAL
ORA-01847: day of month must be between 1 and last day of month
ORA-01847: 月份中的某一天必须介于 1 和月份的最后一天之间
The NVL() descriptionsays:
该NVL（）介绍说：
The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. The implicit conversion is implemented as follows:
If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
If expr1 is numeric, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.
参数 expr1 和 expr2 可以具有任何数据类型。如果它们的数据类型不同，则 Oracle 数据库会隐式地将一种转换为另一种。如果它们不能隐式转换，则数据库返回错误。隐式转换实现如下：
如果 expr1 是字符数据，则 Oracle 数据库在比较它们之前将 expr2 转换为 expr1 的数据类型，并返回 expr1 字符集中的 VARCHAR2。
如果 expr1 是数字，则 Oracle 数据库确定哪个参数具有最高的数字优先级，将另一个参数隐式转换为该数据类型，并返回该数据类型。
So we can simplify the reproduce case:
所以我们可以简化重现的情况：
SELECT TO_DATE('00:00')
FROM DUAL
Since you don't provide a format, it's assuming NLS_DATE_FORMAT, and thus the error: '00' is not a valid day.
由于您不提供格式，因此假设为NLS_DATE_FORMAT，因此错误：'00' 不是有效日期。
(I don't really know what you're trying to do but you can try using pure date functions.)
（我真的不知道您要做什么，但您可以尝试使用纯日期函数。）
回答by Art
Your formatting is need to be fixed. Here's your initial setup:
您的格式需要修复。这是您的初始设置：
SELECT '07-MAR-2013' start_date -- char
, NULL start_time -- char
FROM dual
/
Final formatting - NO NVL required. But if you must then add NVL(start_time, '00:00'):
最终格式 - 不需要 NVL。但是，如果您必须添加 NVL(start_time, '00:00')：
SELECT to_timestamp(start_date||' '||start_time, 'DD-MON-YYYY HH24:MI:SS') t_stamp
FROM
(
SELECT '07-MAR-2013' start_date -- char
, NVL(NULL, '00:00') start_time -- char - NVL is optional
FROM dual
)
/
3/7/2013 12:00:00.000000000 AM
You will get the same result if you remove start_time compl. Generally to compare dates you should use TRUNC to remove time portion.
如果删除 start_time compl，您将获得相同的结果。通常要比较日期，您应该使用 TRUNC 删除时间部分。
SELECT to_timestamp('07-MAR-2013', 'DD-MON-YYYY HH24:MI:SS') start_date_only
FROM dual
/
3/7/2013 12:00:00.000000000 AM
SELECT trunc(to_timestamp('07-MAR-2013', 'DD-MON-YYYY HH24:MI:SS')) start_date_only FROM dual
/
3/7/2013
