在执行带有日期值的IN参数过程时，该月份不是有效的月份

问题描述

CREATE OR REPLACE PROCEDURE PROC1(
   V_STARTTIME    IN TIMESTAMP ,
   V_ENDTIME      IN TIMESTAMP )
BEGIN
INSERT INTO TAB1
SELECT COINS FROM TAB2
WHERE DATE BETWEEN TO_DATE(V_STARTTIME,'mm/dd/yyyy hh:mi:ss aM') AND TO_DATE(V_ENDTIME    ,'mm/dd/yyyy hh:mi:ss aM');
END;

SAMPLE DATE in Tab2 5/5/2014 9:46:38.000000 AM

我的脚本在一系列日期之间运行.这两个日期是IN参数.

My script runs between a range of dates. The two dates are IN parameters.

执行程序时

执行proc1('5/05/2014 11:25:00 AM'，'5/05/2014 12:25:00 PM')

Execute proc1('5/05/2014 11:25:00 AM','5/05/2014 12:25:00 PM')

我没有收到有效的月份错误.任何想法如何解决这个问题?谢谢

I am getting not a valid month error.Any idea how to fix this?Thanks

推荐答案

您的过程采用类型为timestamp的参数.您实际上是在调用中传递varchar2类型的参数.这迫使Oracle使用会话的NLS_TIMESTAMP_FORMAT将varchar2参数隐式转换为timestamp.对于不同的会话，这可能会有所不同，因此，至少某些会话可能会出错，因为字符串与该会话的NLS_TIMESTAMP_FORMAT格式不匹配.最好通过显式调用to_timestamp或传递时间戳文字来传递实际时间戳.

Your procedure takes parameters of type timestamp. You're actually passing parameters of type varchar2 in your call. That forces Oracle to perform implicit conversion of the varchar2 parameters to timestamp using your session's NLS_TIMESTAMP_FORMAT. That will likely be different for different sessions so it is likely that at least some sessions will get an error because the string doesn't match the format of that session's NLS_TIMESTAMP_FORMAT. You'd be much better served passing in an actual timestamp either by explicitly calling to_timestamp or by passing a timestamp literal.

然后，您的过程将使用timestamp参数，并将它们传递给to_date函数. to_date函数不采用类型为timestamp的参数，而仅采用类型为varchar2的参数.这迫使Oracle再次使用会话的NLS_TIMESTAMP_FORMAT将timestamp参数再次隐式转换为varchar2.如果会话的NLS_TIMESTAMP_FORMAT与您的to_date调用中的显式格式掩码不匹配，则将出现错误，或者转换将返回意外的结果.

Your procedure then takes the timestamp parameters and pass them to the to_date function. The to_date function does not take parameters of type timestamp, it only takes parameters of type varchar2. That forces Oracle to do another implicit conversion of the timestamp parameters to varchar2, again using the session's NLS_TIMESTAMP_FORMAT. If the session's NLS_TIMESTAMP_FORMAT doesn't match the explicit format mask in your to_date call, you'll get an error or the conversion will return a result that you don't expect.

如果表中的列实际上是date类型，则可以直接将date与timestamp进行比较.因此，似乎没有任何理由在此处调用to_date.但是，基于示例数据，表中的列实际上是类型timestamp，而不是代码所暗示的date类型，因为date的秒精度不高.如果是这种情况，那么在您的SELECT语句中调用to_date的意义就更小了，因为您的参数实际上是timestamp类型，而列的类型是timestamp.只需比较timestamp值即可.

If the column in your table is actually of type date, you can directly compare a date to a timestamp. So there doesn't appear to be any reason to call to_date here. Based on your sample data, though, it appears that the column in your table is actually of type timestamp rather than date as your code implies, since a date does not have fractional seconds of precision. If that's the case, it makes even less sense to call to_date in your SELECT statement since your parameters are actually of type timestamp and your column is of type timestamp. Just compare the timestamp values.

因此，我的猜测是您想要类似的东西

My guess, therefore, is that you want something like

CREATE OR REPLACE PROCEDURE PROC1(
   V_STARTTIME    IN TIMESTAMP ,
   V_ENDTIME      IN TIMESTAMP )
BEGIN
  INSERT INTO TAB1( <<column name>> )
    SELECT COINS
      FROM TAB2
     WHERE <<timestamp column name>> BETWEEN v_starttime AND v_endtime;
END;

，并且您希望通过传递实际时间戳来校准过程.使用时间戳文字

and that you want to cal the procedure by passing actual timestamps. Using timestamp literals

Execute proc1(timestamp '2014-05-05 11:25:00', timestamp '2014-05-05 12:25:00' )

或通过显式调用to_timestamp

execute proc1( to_timestamp( '5/05/2014 11:25:00 AM', 'MM/DD/YYYY HH:MI:SS AM' ),
               to_timestamp( '5/05/2014 12:25:00 PM', 'MM/DD/YYYY HH:MI:SS AM' ) );

这应该消除当前正在发生的所有隐式类型转换.

That should get rid of all the implicit type conversions that are currently taking place.

这篇关于在执行带有日期值的IN参数过程时，该月份不是有效的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！