使用日期筛选下载Excel时遇ORA-01830错误求助

问题背景 #

实现基于日期筛选的Excel下载功能，但调用存储过程时触发错误：

ORA-01830: date format picture ends before converting entire input string

相关代码与配置 #

前端日期选择代码 #

<script type="text/javascript">
    $(function () {
        $('#ContentPlaceHolder1_txtDateSelection').datepicker(
        {
            dateFormat: 'dd-mm-yy',
            changeMonth: true,
            changeYear: true,
            yearRange: '1950:2100',
            maxDate: 0
        });
    })
</script>

前端控件与后端事件代码 #

<input type="text" id="txtDateSelection" runat="server" class="form-control" readonly="readonly" />    

<asp:Button ID="btnDownloadInfo" runat="server" Text="Report Download" OnClick="btnDownloadInfo_Click" class="btn btn-primary downnloadReport" />    

protected void btnDownloadInfo_Click(object sender, EventArgs e)
{
    DataTable dtExcelData = new DataTable();
    try
    {
        CommonUser ObjUser = new CommonUser();
        string strDateFilter = txtDateSelection.Value;
        dtExcelData = ObjUser.GET_EXCEL_REPORT(strDateFilter);

        if (dtExcelData != null && dtExcelData.Rows.Count > 0)
        {
            DownloadReport(dtExcelData);
        }
        else
        {
            ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('No record found');", true);
        }
        
    }
    catch (Exception ex)
    {
        string strErrorMsg = ex.Message.ToString() + " " + "StackTrace :" + ex.StackTrace.ToString();
        CommonDB.WriteLog("ERROR:" + strErrorMsg, ConfigurationManager.AppSettings["AIRFIBER_LOG"].ToString());
    }
}


public DataTable GET_EXCEL_REPORT(string strDateValue)
{
    OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionStringAirFiber"].ConnectionString);
    DataTable dtIpColo = new DataTable();

    try
    {
        OracleCommand cmd = new OracleCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = ConfigurationManager.AppSettings["GET_EXCEL_REPORT_INFO"].ToString();
        cmd.Connection = conn;

        cmd.Parameters.Add(new OracleParameter { ParameterName = "P_DATE", Value = strDateValue, OracleDbType = OracleDbType.NVarchar2, Direction = ParameterDirection.Input });
        cmd.Parameters.Add(new OracleParameter { ParameterName = "P_CURR", OracleDbType = OracleDbType.RefCursor, Direction = ParameterDirection.Output });

        if (conn.State != ConnectionState.Open) conn.Open();
        OracleDataAdapter daIpColo = new OracleDataAdapter(cmd);
        daIpColo.Fill(dtIpColo);
    }
    catch (Exception)
    {
        throw;
    }
    return dtIpColo;
}        

数据库表结构 #

Table name:- LOS_REQUEST_MANUAL

Name                Null?    Type           
------------------- -------- -------------
ENB_SITE_ID                  VARCHAR2(30)  
GIS_SECTOR_ID                VARCHAR2(30)  
PMP_SAP_ID                   VARCHAR2(30)  
END_SECTOR_ID                VARCHAR2(30)  
A6_NEID             NOT NULL VARCHAR2(30)  
AZIMUTH                      NUMBER        
HEIGHT                       NUMBER        
VER_BEAM_WIDTH_TILT          NUMBER        
VER_BEAM_WIDTH               NUMBER        
HORIZANTAL_BEAM              NUMBER        
DISTNACE_OF_CONE             NUMBER        
SOURCE                       VARCHAR2(30)  
STATUS                       VARCHAR2(30)  
REMARKS                      VARCHAR2(250) 
MODIFIED_DATE                DATE          
USERNAME                     VARCHAR2(200)

数据库NLS配置 #

Parameter: NLS_DATE_FORMAT   value: DD-MON-RR

错误原因分析 #

1. 前端传入的日期格式为dd-mm-yy（示例：25-10-2024），但Oracle数据库默认日期格式是DD-MON-RR（示例：25-OCT-24），两者格式不匹配。
2. 当前代码将字符串类型的日期传给存储过程，若存储过程中P_DATE为DATE类型，Oracle会尝试用默认格式隐式解析字符串，解析失败触发ORA-01830错误。
3. 代码中把P_DATE参数类型设为NVarchar2，进一步增加了隐式转换的风险。

解决方案 #

方案一：前端输出数据库兼容格式 #

修改前端datepicker的日期格式，直接输出Oracle匹配的格式：

dateFormat: 'dd-M-yy'

输出示例：25-Oct-24，与数据库NLS格式一致，Oracle可直接解析。

方案二：后端转换为DateTime类型传入 #

在C#代码中将前端字符串转换为DateTime类型，再以DATE类型参数传入，避免字符串解析错误：

public DataTable GET_EXCEL_REPORT(string strDateValue)
{
    OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionStringAirFiber"].ConnectionString);
    DataTable dtIpColo = new DataTable();

    try
    {
        // 按前端格式解析字符串为DateTime
        DateTime filterDate = DateTime.ParseExact(strDateValue, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture);

        OracleCommand cmd = new OracleCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = ConfigurationManager.AppSettings["GET_EXCEL_REPORT_INFO"].ToString();
        cmd.Connection = conn;

        // 参数类型改为OracleDbType.Date
        cmd.Parameters.Add(new OracleParameter { ParameterName = "P_DATE", Value = filterDate, OracleDbType = OracleDbType.Date, Direction = ParameterDirection.Input });
        cmd.Parameters.Add(new OracleParameter { ParameterName = "P_CURR", OracleDbType = OracleDbType.RefCursor, Direction = ParameterDirection.Output });

        if (conn.State != ConnectionState.Open) conn.Open();
        OracleDataAdapter daIpColo = new OracleDataAdapter(cmd);
        daIpColo.Fill(dtIpColo);
    }
    catch (Exception)
    {
        throw;
    }
    return dtIpColo;
}        

注：若前端是两位年份，解析格式改为"dd-MM-yy"，建议统一使用四位年份避免歧义。

方案三：存储过程显式指定解析格式 #

若无法修改前后端代码，可在存储过程中用TO_DATE函数指定解析格式：

SELECT * FROM LOS_REQUEST_MANUAL
WHERE MODIFIED_DATE = TO_DATE(P_DATE, 'DD-MM-YYYY')

强制按前端传入的格式解析字符串，不受数据库NLS配置影响。

最佳实践建议 #

• 优先传递DateTime类型参数，避免字符串到日期的隐式转换，减少格式匹配问题。
• 统一前后端与数据库的日期格式，或在转换时显式指定格式，不要依赖数据库NLS配置（配置可能变更）。

内容的提问来源于stack exchange，提问作者Nadeem