将VARCHAR类型日期转换为指定格式并实现Date3的日期范围过滤

1. 日期格式转换（VARCHAR转YY-MM-DD） #

原字段存储格式为YYYYMMDD（注意你的Date3示例2017028属于格式错误，应为20170228，否则转换会失败），需先转为日期类型再格式化，推荐用CONVERT（SQL Server）或DATE_FORMAT（MySQL）实现：

SQL Server 写法 #

SELECT
    first_name,
    last_name,
    department,
    -- 转换为YY-MM-DD格式
    REPLACE(CONVERT(VARCHAR(8), CAST(date1 AS DATE), 2), '.', '-') AS formatted_date1,
    REPLACE(CONVERT(VARCHAR(8), CAST(date2 AS DATE), 2), '.', '-') AS formatted_date2,
    REPLACE(CONVERT(VARCHAR(8), TRY_CAST(date3 AS DATE), 2), '.', '-') AS formatted_date3
FROM 
    Data

• CONVERT参数2对应YY.MM.DD格式，用REPLACE替换.为-得到目标格式
• 用TRY_CAST替代CAST，避免因格式错误的字段导致整个查询报错

MySQL 写法 #

SELECT
    first_name,
    last_name,
    department,
    DATE_FORMAT(STR_TO_DATE(date1, '%Y%m%d'), '%y-%m-%d') AS formatted_date1,
    DATE_FORMAT(STR_TO_DATE(date2, '%Y%m%d'), '%y-%m-%d') AS formatted_date2,
    DATE_FORMAT(STR_TO_DATE(date3, '%Y%m%d'), '%y-%m-%d') AS formatted_date3
FROM Data

2. 修正Date3范围筛选逻辑 #

你之前的筛选存在两个问题：一是用GETDATE()-730无法精准对应2021-01-01；二是直接CAST可能因格式错误报错。以下是正确的筛选条件：

SQL Server 完整查询 #

SELECT
    first_name,
    last_name,
    department,
    REPLACE(CONVERT(VARCHAR(8), CAST(date1 AS DATE), 2), '.', '-') AS formatted_date1,
    REPLACE(CONVERT(VARCHAR(8), CAST(date2 AS DATE), 2), '.', '-') AS formatted_date2,
    REPLACE(CONVERT(VARCHAR(8), TRY_CAST(date3 AS DATE), 2), '.', '-') AS formatted_date3
FROM 
    Data
WHERE 
    department = 'accounting'
    AND TRY_CAST(date3 AS DATE) >= '2021-01-01'
    AND TRY_CAST(date3 AS DATE) <= DATEADD(DAY, 30, GETDATE())

MySQL 完整查询 #

SELECT
    first_name,
    last_name,
    department,
    DATE_FORMAT(STR_TO_DATE(date1, '%Y%m%d'), '%y-%m-%d') AS formatted_date1,
    DATE_FORMAT(STR_TO_DATE(date2, '%Y%m%d'), '%y-%m-%d') AS formatted_date2,
    DATE_FORMAT(STR_TO_DATE(date3, '%Y%m%d'), '%y-%m-%d') AS formatted_date3
FROM Data
WHERE
    department = 'accounting'
    AND STR_TO_DATE(date3, '%Y%m%d') >= '2021-01-01'
    AND STR_TO_DATE(date3, '%Y%m%d') <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)

额外处理：修复格式错误的Date3字段 #

如果存在2017028这类长度为7的错误日期（缺一位日部分），可以先补全再转换：

-- SQL Server补全逻辑
TRY_CAST(CASE WHEN LEN(date3)=7 THEN STUFF(date3,6,0,'0') ELSE date3 END AS DATE)

-- MySQL补全逻辑
STR_TO_DATE(CASE WHEN CHAR_LENGTH(date3)=7 THEN INSERT(date3,6,0,'0') ELSE date3 END, '%Y%m%d')

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