oracle日期时间段查询遇到问题to_date ora-01847 day of month must be between 1 and last day of month

最新推荐文章于 2022-03-09 14:36:06 发布

马可菠萝me

最新推荐文章于 2022-03-09 14:36:06 发布

阅读量1w

点赞数

分类专栏： javaWeb 文章标签： oracle 函数

本文链接：https://blog.csdn.net/nxdxmg/article/details/47980075

版权

javaWeb 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

oracle日期时间段查询时遇到问题：to_date ora-01847 day of month must be between 1 and last day of month

上网查资料得到如下：

select to_timestamp(to_char(start_date1,'DD-MON-YYYY')||' '||NVL(start_time1,'00:00'),'DD-MON-YYYY HH24:MI') from table1

资料有如下描述：

The NVL() description says:

    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.

意思是，在同一个SQL语句中，时间参数的类型（格式）要一致。如果不一致，oracle就会把一个类型转换成另一种类型，若转换失败就会抛出异常。所以当出现该问题时，一定要检查整句SQL语句中的日期时间格式，问题便会迎刃而解。

修改格式化函数to_date 的函数格式后，问题解决。