如何将SQL同一列中DMMYYYY格式VARCHAR字符串转为DD/MM/YYYY日期类型

问题分析 #

你的表中DQ（或TIMESTAMP）列存储的是DMMYYYY格式的字符串（日可能是1位/2位，月固定2位，年固定4位），需要转换为DD/MM/YYYY格式的日期类型。

修正后的SQL代码 #

假设要转换的列是DQ，可以通过以下方式处理：

SELECT 
    [serial],
    [srn],
    [ticker],
    [series],
    [QT],
    [DQ],
    [DQ/QT],
    -- 生成DD/MM/YYYY格式的字符串
    CASE
        WHEN LEN([DQ]) = 7 THEN STUFF(STUFF([DQ], 2, 0, '/'), 5, 0, '/')
        WHEN LEN([DQ]) = 8 THEN STUFF(STUFF([DQ], 3, 0, '/'), 6, 0, '/')
    END AS FormattedDQ,
    -- 转换为DATE类型的日期（需要DATETIME可替换为DATETIME）
    CONVERT(DATE, 
        CASE
            WHEN LEN([DQ]) = 7 THEN STUFF(STUFF([DQ], 2, 0, '/'), 5, 0, '/')
            WHEN LEN([DQ]) = 8 THEN STUFF(STUFF([DQ], 3, 0, '/'), 6, 0, '/')
        END,
        103  -- 103是SQL Server中DD/MM/YYYY对应的格式代码
    ) AS ConvertedDQDate
FROM [Indexer].[dbo].[EquityNSEDAT]

代码说明 #

1. 字符串格式化：
   • 原字符串长度为7时（如8012005），在第2位后插入/，第5位后插入/，得到08/01/2005
   • 原字符串长度为8时（如10012005），在第3位后插入/，第6位后插入/，得到10/01/2005
2. 转换为日期类型：
   • 使用CONVERT函数指定目标类型为DATE，格式代码103对应DD/MM/YYYY格式，确保SQL Server能正确识别转换后的字符串为日期。

你之前代码的问题 #

1. 错误使用TIMESTAMP类型：SQL Server中的TIMESTAMP是二进制行版本标识符，不是日期时间类型，应该用DATE或DATETIME。
2. CONVERT语法错误：SQL Server的CONVERT语法是CONVERT(目标类型, 转换表达式, 格式代码)，你未指定要转换的列，且误用了MySQL风格的格式字符串（%D/%m/%y），SQL Server使用数字格式代码。
3. 未处理字符串长度差异：原数据存在7位和8位两种长度，必须分别处理才能正确拆分日、月、年。

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