如何解决SQL错误:ORA-01843:不是有效月份

Question

UPDATE Table1
SET CHANGE_DELIVERY_REASON    = 'Card Activation',
CHANGE_DELIVERY_NOTE        = 'ACTIVATION SUCCESS [1 OK - 2 OK]',
RECONCILIATION_STATUS       = 1 ,
RECONCILIATION_LAST_UPDATED = '23/4/2018 12:00:00 AM',
RECONCILIATION_COUNT        = '6',
ACTIVATION_FAILURE          = 'SUCCESS';

“SQL错误：ORA-01843:无效月份”

我不知道我的查询有什么问题。

updateQuery = " UPDATE " + clsUtility.GetMasterTable() + " SET ";
updateQuery += "   CHANGE_DELIVERY_REASON = 'Card Activation', ";
updateQuery += "   CHANGE_DELIVERY_NOTE = '" + notes + "', ";
updateQuery += "   RECONCILIATION_STATUS = 1 , ";
updateQuery += "   RECONCILIATION_LAST_UPDATED = '" + DateTime.Today + "', ";
updateQuery += "   RECONCILIATION_COUNT = '" + reconciliation_count + "',";
updateQuery += "   ACTIVATION_FAILURE = '" + resultPart + "', ";

Answer 1

你的问题是你给出的日期的格式，这在其他答案中已经处理过了。您将需要其他日期字段，所以一定要看看它。

但是，对于这种特殊情况，您可以简单地停止使用本地日期，让数据库完成以下工作：

RECONCILIATION_LAST_UPDATED = trunc(sysdate)

另外，请在查询中使用参数绑定。您很容易受到SQL注入攻击。

using (var connection = new SqlConnection())
{
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "UPDATE " + clsUtility.GetMasterTable() + " SET RECONCILIATION_LAST_UPDATED = :last_updated, CHANGE_DELIVERY_NOTE = :delivery_note";

        var parameterLastUpdated = command.CreateParameter();

        parameterLastUpdated.ParameterName = ":last_updated";
        parameterLastUpdated.SqlDbType = SqlDbType.DateTime;
        parameterLastUpdated.Direction = ParameterDirection.Input;
        parameterLastUpdated.Value = DateTime.Today;

        command.Parameters.Add(parameterLastUpdated);

        var parameterDelivery = command.CreateParameter();

        parameterDelivery.ParameterName = ":delivery_note";
        parameterDelivery.SqlDbType = SqlDbType.NVarChar;
        parameterDelivery.Direction = ParameterDirection.Input;
        parameterDelivery.Value = "Some string you want in notes";

        command.Parameters.Add(parameterDelivery);

        command.ExecuteNonQuery();
    }
}