oracle中使用round函数报错无效数字

[A Growing Newbie]

SQL> select ROUND('25-JUL-95','MONTH') from dual;

select ROUND('25-JUL-95','MONTH') from dual

ORA-01722: 无效数字

[zbdzjx]

用to_date，将'25-JUL-95'转换成日期格式。

[A Growing Newbie]

但是ADD_MONTHS ('11-JAN-94',6)这样是可以的add_months()的第一个参数也是要求为date型@zbdzjx

[js14982]

'25-JUL-95' 这个是字符型的值，不是date类型，不能这样用

[js14982]

select ROUND(date'1995-7-25','MONTH') from dual; 可以这样

[A Growing Newbie]

亲测有效： SQL> select ADD_MONTHS ('11-JAN-94',6) from dual; ADD_MONTHS('11-JAN-94',6) ------------------------- 1994/7/11 SQL> @js14982

[zbdzjx]

引用 2 楼 qq_25419283 的回复:

但是ADD_MONTHS ('11-JAN-94',6)这样是可以的add_months()的第一个参数也是要求为date型@zbdzjx

不知是不是语言不正确，我执行了“select ADD_MONTHS ('11-JAN-94',6) from dual”，提示是： select ADD_MONTHS ('11-JAN-94',6) from dual * Error at line 1 ORA-01843: 不是有效的月份

[js14982]

SQL> select ADD_MONTHS('11-JAN-94',6) from dual; select ADD_MONTHS('11-JAN-94',6) from dual * 第 1 行出现错误: ORA-01843: 无效的月份

[A Growing Newbie]

引用 6 楼 zbdzjx 的回复:

[quote=引用 2 楼 qq_25419283 的回复:] 但是ADD_MONTHS ('11-JAN-94',6)这样是可以的add_months()的第一个参数也是要求为date型@zbdzjx

不知是不是语言不正确，我执行了“select ADD_MONTHS ('11-JAN-94',6) from dual”，提示是： select ADD_MONTHS ('11-JAN-94',6) from dual * Error at line 1 ORA-01843: 不是有效的月份 [/quote] 你要执行alter session set nls_date_language='american';才行@zbdzjx

[A Growing Newbie]

引用 7 楼 js14982 的回复:

SQL> select ADD_MONTHS('11-JAN-94',6) from dual; select ADD_MONTHS('11-JAN-94',6) from dual * 第 1 行出现错误: ORA-01843: 无效的月份

你要执行alter session set nls_date_language='american';才行 @js14982

[ghx287524027]

这是oracle官方给出的关于 add_months() 函数的说明： ADD_MONTHS returns the date date plus integer months. A month is defined by the session parameter NLS_CALENDAR. The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument can be an integer or any value that can be implicitly converted to an integer. The return type is always DATE, regardless of the data type of date. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date. 从中可以看出，对于add_months() 的第一个参数，可以是date类型，也可以是可以隐式转换为date的其他类型。 同样，下面是关于 round() 函数的官方说明： ROUND returns date rounded to the unit specified by the format model fmt. This function is not sensitive to the NLS_CALENDAR session parameter. It operates according to the rules of the Gregorian calendar. The value returned is always of data type DATE, even if you specify a different datetime data type for date. If you omit fmt, then date is rounded to the nearest day. The date expression must resolve to a DATE value.