将MMddyyyy格式VARCHAR转换为MM/dd/yy日期时遇FORMAT函数报错求助

问题根源 #

你创建临时表时，TempDate字段继承了原表charvalue的VARCHAR类型——即便用DATEFROMPARTS生成了日期值，赋值给VARCHAR字段后仍会以字符串形式存储。而FORMAT函数要求第一个参数必须是日期/时间类型，因此抛出了数据类型不匹配的错误。

解决方案一：创建临时表时直接定义日期类型 #

直接在SELECT INTO阶段将TempDate转换为DATE类型，后续无需额外更新，排序和格式化都更可靠：

select  f.filenumber as claimnumber, 
        f.filename as Name,
        f.charvalue as OtherDate,
        -- 直接转换为DATE类型存入临时表
        DATEFROMPARTS(RIGHT(f.charvalue,4), LEFT(f.charvalue, 2), SUBSTRING(f.charvalue, 3, 2)) as TempDate
into #TempClaims
from [Table] f
where ISNUMERIC(f.charvalue) = 1
  and LEN(f.charvalue) = 8
  and f.charvalue not like '%.%'

-- 直接格式化日期并按日期类型排序
Select claimnumber, Name, OtherDate,  
       FORMAT(TempDate, 'MM/dd/yy') as DateOfLoss 
From #TempClaims
order by TempDate

解决方案二：查询时先转换为日期类型再格式化 #

如果不想修改临时表创建逻辑，可在查询阶段将字符串类型的TempDate转为DATE类型后再使用FORMAT：

-- 保留你原有的临时表创建和更新语句
select  f.filenumber as claimnumber, 
        f.filename as Name,
        f.charvalue as OtherDate,
        f.charvalue as TempDate
into #TempClaims
from [Table] f
where ISNUMERIC(f.charvalue) = 1
  and LEN(f.charvalue) = 8
  and f.charvalue not like '%.%'

Update #TempClaims
set TempDate = DATEFROMPARTS(RIGHT(TempDate,4), LEFT(tempdate, 2), SUBSTRING(TempDate, 3, 2)) 

-- 查询时先转换为DATE类型再格式化
Select claimnumber, Name, OtherDate,  
       FORMAT(CAST(TempDate AS DATE), 'MM/dd/yy') as DateOfLoss 
From #TempClaims
order by CAST(TempDate AS DATE)

优化：过滤无效日期 #

ISNUMERIC无法确保8位数字是有效日期（比如02302024这类不存在的日期），可使用TRY_CONVERT过滤转换失败的无效数据：

select  f.filenumber as claimnumber, 
        f.filename as Name,
        f.charvalue as OtherDate,
        -- 将MMddyyyy转为MM/dd/yyyy格式后尝试转换为DATE
        TRY_CONVERT(DATE, STUFF(STUFF(f.charvalue, 5, 0, '/'), 3, 0, '/'), 101) as TempDate
into #TempClaims
from [Table] f
where ISNUMERIC(f.charvalue) = 1
  and LEN(f.charvalue) = 8
  and f.charvalue not like '%.%'
  -- 只保留转换成功的有效日期
  and TRY_CONVERT(DATE, STUFF(STUFF(f.charvalue, 5, 0, '/'), 3, 0, '/'), 101) is not null

Select claimnumber, Name, OtherDate,  
       FORMAT(TempDate, 'MM/dd/yy') as DateOfLoss 
From #TempClaims
order by TempDate

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