如何在C#中提取DataTable日期列的年月并更新至新表

我有一个DataTable，其中DATA_WITH_MONTH_YEAR列的取值格式为01-04-2023 00:00:00，希望从中提取04-2023格式的年月信息，并将其更新到新的DataTable中。同时附上相关INSERT语句：

INSERT INTO TBL_IPCOLO_BILLING_MST (POLITICAL_STATE_NAME,POLITICAL_STATE_CODE,RFCDATE,RFS_DATE,RFE1_DATE,SITE_DROP_DATE,IP_COLO_SITEID,MW_INSTALLED,DG_NONDG,EB_NONEB,ID_OD_COUNTCHANGE,ID_OD_CHANGEDDATE,RRH_COUNTCHANGE,RRH_CHANGEDDATE,TENANCY_COUNTCHANGE,TENANCY_CHANGEDDATE,SAP_ID,CREATED_BY,RFS_DATE_5G,DROP_DATE_5G,OLT_COUNT,OLT_CHANGE_DATE,DIESEL_DOWNTIME_MINUTES,OVERALL_INFRA_OUTAGE_MINUTES,DIESEL_DOWNTIME_MIN_MY,OVERALL_INFRA_OUTAGE_MIN_MY) VALUES (:POLITICAL_STATE_NAME,:POLITICAL_STATE_CODE,:RFCDATE,:RFS_DATE,:RFE1_DATE,:SITE_DROP_DATE,:IP_COLO_SITEID,:MW_INSTALLED,:DG_NONDG,:EB_NONEB,:ID_OD_COUNTCHANGE,:ID_OD_CHANGEDDATE,:RRH_COUNTCHANGE,:RRH_CHANGEDDATE,:TENANCY_COUNTCHANGE,:TENANCY_CHANGEDDATE,:SAP_ID,:CREATED_BY,:RFS_DATE_5G,:DROP_DATE_5G,:OLT_COUNT,:OLT_CHANGE_DATE,:DIESEL_DOWNTIME_MINUTES,:OVERALL_INFRA_OUTAGE_MINUTES,:TO_CHAR(DIESEL_DOWNTIME_MINUTES_MON_YEAR, 'MM:YYYY') AS DIESEL_DOWNTIME_MINUTES_MON_YEAR,:OVERALL_INFRA_OUTAGE_MINUTES_MON_YEAR)

分两种场景处理，根据实际需求选择：

一、内存中操作DataTable（以C#为例） #

如果是在程序内存里处理DataTable，直接解析日期字符串提取年月：

1. 复制原DataTable结构，新增存储年月的列（比如MONTH_YEAR）
2. 遍历原表每一行，解析DATA_WITH_MONTH_YEAR列的字符串为DateTime类型
3. 将日期格式化为MM-yyyy字符串，写入新表对应列

示例代码：

// 原DataTable
DataTable originalDt = GetOriginalDataTable(); // 替换为你的原表获取逻辑
// 创建新表，复制原结构并添加年月列
DataTable newDt = originalDt.Clone();
newDt.Columns.Add("MONTH_YEAR", typeof(string));

foreach (DataRow row in originalDt.Rows)
{
    DataRow newRow = newDt.NewRow();
    // 复制原行所有数据
    foreach (DataColumn col in originalDt.Columns)
    {
        newRow[col.ColumnName] = row[col.ColumnName];
    }
    // 解析日期并提取年月
    string dateStr = row["DATA_WITH_MONTH_YEAR"].ToString().Trim();
    if (DateTime.TryParseExact(dateStr, "dd-MM-yyyy HH:mm:ss", null, System.Globalization.DateTimeStyles.None, out DateTime targetDt))
    {
        newRow["MONTH_YEAR"] = targetDt.ToString("MM-yyyy");
    }
    else
    {
        // 处理解析失败的异常情况，比如设为NULL或默认值
        newRow["MONTH_YEAR"] = DBNull.Value;
    }
    newDt.Rows.Add(newRow);
}

二、数据库层面处理（结合INSERT语句） #

从你提供的TO_CHAR函数判断是Oracle数据库，可以直接在SQL层面完成年月提取，无需在程序中处理：

注意：原INSERT语句存在语法错误 #

原语句中VALUES子句里的:TO_CHAR(...) AS ...写法不合法，AS别名不能出现在VALUES中，需修正为直接格式化值。

修正后的两种写法： #

1. 从源表查询插入

如果是从其他表查询数据插入目标表，直接在SELECT中格式化日期：

INSERT INTO TBL_IPCOLO_BILLING_MST (
    POLITICAL_STATE_NAME, POLITICAL_STATE_CODE, RFCDATE, RFS_DATE, RFE1_DATE,
    SITE_DROP_DATE, IP_COLO_SITEID, MW_INSTALLED, DG_NONDG, EB_NONEB,
    ID_OD_COUNTCHANGE, ID_OD_CHANGEDDATE, RRH_COUNTCHANGE, RRH_CHANGEDDATE,
    TENANCY_COUNTCHANGE, TENANCY_CHANGEDDATE, SAP_ID, CREATED_BY, RFS_DATE_5G,
    DROP_DATE_5G, OLT_COUNT, OLT_CHANGE_DATE, DIESEL_DOWNTIME_MINUTES,
    OVERALL_INFRA_OUTAGE_MINUTES, DIESEL_DOWNTIME_MIN_MY, OVERALL_INFRA_OUTAGE_MIN_MY
)
SELECT
    POLITICAL_STATE_NAME, POLITICAL_STATE_CODE, RFCDATE, RFS_DATE, RFE1_DATE,
    SITE_DROP_DATE, IP_COLO_SITEID, MW_INSTALLED, DG_NONDG, EB_NONEB,
    ID_OD_COUNTCHANGE, ID_OD_CHANGEDDATE, RRH_COUNTCHANGE, RRH_CHANGEDDATE,
    TENANCY_COUNTCHANGE, TENANCY_CHANGEDDATE, SAP_ID, CREATED_BY, RFS_DATE_5G,
    DROP_DATE_5G, OLT_COUNT, OLT_CHANGE_DATE, DIESEL_DOWNTIME_MINUTES,
    OVERALL_INFRA_OUTAGE_MINUTES,
    -- 从DATA_WITH_MONTH_YEAR提取MM-YYYY格式的年月
    TO_CHAR(TO_DATE(DATA_WITH_MONTH_YEAR, 'DD-MM-YYYY HH24:MI:SS'), 'MM-YYYY'),
    OVERALL_INFRA_OUTAGE_MIN_MY
FROM YOUR_SOURCE_TABLE; -- 替换为实际的源表名

2. 参数绑定插入

如果是程序通过参数绑定插入数据，在程序中先将DATA_WITH_MONTH_YEAR解析为MM-yyyy字符串，再绑定到:DIESEL_DOWNTIME_MIN_MY参数即可，INSERT语句简化为：

INSERT INTO TBL_IPCOLO_BILLING_MST (
    POLITICAL_STATE_NAME, POLITICAL_STATE_CODE, RFCDATE, RFS_DATE, RFE1_DATE,
    SITE_DROP_DATE, IP_COLO_SITEID, MW_INSTALLED, DG_NONDG, EB_NONEB,
    ID_OD_COUNTCHANGE, ID_OD_CHANGEDDATE, RRH_COUNTCHANGE, RRH_CHANGEDDATE,
    TENANCY_COUNTCHANGE, TENANCY_CHANGEDDATE, SAP_ID, CREATED_BY, RFS_DATE_5G,
    DROP_DATE_5G, OLT_COUNT, OLT_CHANGE_DATE, DIESEL_DOWNTIME_MINUTES,
    OVERALL_INFRA_OUTAGE_MINUTES, DIESEL_DOWNTIME_MIN_MY, OVERALL_INFRA_OUTAGE_MIN_MY
) VALUES (
    :POLITICAL_STATE_NAME, :POLITICAL_STATE_CODE, :RFCDATE, :RFS_DATE, :RFE1_DATE,
    :SITE_DROP_DATE, :IP_COLO_SITEID, :MW_INSTALLED, :DG_NONDG, :EB_NONEB,
    :ID_OD_COUNTCHANGE, :ID_OD_CHANGEDDATE, :RRH_COUNTCHANGE, :RRH_CHANGEDDATE,
    :TENANCY_COUNTCHANGE, :TENANCY_CHANGEDDATE, :SAP_ID, :CREATED_BY, :RFS_DATE_5G,
    :DROP_DATE_5G, :OLT_COUNT, :OLT_CHANGE_DATE, :DIESEL_DOWNTIME_MINUTES,
    :OVERALL_INFRA_OUTAGE_MINUTES, :FORMATTED_MONTH_YEAR, :OVERALL_INFRA_OUTAGE_MIN_MY
);

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