SQL Server中如何将YYYYMM格式转换为当月首日日期？

要将DateCompleted列从YYYYMM格式转换为当月第一天的YYYY-MM-DD格式，你需要修改子查询中该字段的生成逻辑，同时同步调整WHERE子句中的日期比较条件，具体操作如下：

1. 修改DateCompleted的生成逻辑 #

原查询通过字符串拼接生成YYYYMM格式的DateCompleted，我们可以直接用SQL Server的DATEFROMPARTS函数（适用于2012及以上版本）构造当月第一天的日期值，避免字符串转换的潜在问题：

原代码片段：

concat(d.TransfCompYear, case when len(d.TransfCompPeriod) = 1 then '0' end, d.TransfCompPeriod) as DateCompleted

替换为：

-- 直接生成日期类型的当月第一天，默认显示为YYYY-MM-DD格式
DATEFROMPARTS(d.TransfCompYear, d.TransfCompPeriod, 1) as DateCompleted

如果需要强制输出字符串格式的YYYY-MM-DD，可以用CONVERT或FORMAT函数包装：

-- 使用CONVERT固定格式输出字符串
CONVERT(varchar(10), DATEFROMPARTS(d.TransfCompYear, d.TransfCompPeriod, 1), 23) as DateCompleted

-- 或者使用FORMAT函数（SQL Server 2012+支持）
FORMAT(DATEFROMPARTS(d.TransfCompYear, d.TransfCompPeriod, 1), 'yyyy-MM-dd') as DateCompleted

2. 同步调整WHERE子句的日期比较条件 #

由于DateCompleted现在是日期类型，需要将原条件中的字符串比较改为日期比较，确保逻辑正确：

原条件：

and i.DateCompleted >= convert(varchar(6),getdate()-90,112)

替换为：

and i.DateCompleted >= DATEFROMPARTS(YEAR(DATEADD(day, -90, GETDATE())), MONTH(DATEADD(day, -90, GETDATE())), 1)

完整修改后的查询语句 #

select
DateCompleted,
GtrReference,
SourceWarehouse,
TargetWarehouse,
DateCreated,
EntryType,
ControlAccount,
InitialValue,
Operator,
FirstDesc,
SecondDesc
from
(
    select
    -- 核心修改：生成当月第一天的日期
    DATEFROMPARTS(d.TransfCompYear, d.TransfCompPeriod, 1) as DateCompleted,
    a.Complete,
    d.Line,
    d.TransfCompPeriod as DateMonth,
    d.TransfCompYear as DateYear,
    a.GtrReference as GtrReference,
    a.SourceWarehouse as SourceWarehouse, 
    max(a.TargetWarehouse) over (partition by a.GtrReference) as TargetWarehouse, 
    a.DateCreated as DateCreated,
    COALESCE(d.ExpectedDueDate, d.ExpectedDueDate) as ExpectedDueDate,
    a.EntryType as EntryType, 
    a.ControlAccount as ControlAccount, 
    max(a.InitialValue) over (partition by a.GtrReference) as InitialValue,
    a.Operator as Operator,
    b.Description as FirstDesc, 
    c.Description as SecondDesc
FROM 
    [GtrMaster] a  
    LEFT JOIN [InvWhControl] b  
     ON (a.SourceWarehouse = b.Warehouse) 
    LEFT JOIN [InvWhControl] c  
     ON (a.TargetWarehouse = c.Warehouse) 
    LEFT JOIN [GtrDetail] d  
     ON (a.GtrReference = d.GtrReference) 
) as i
WHERE ( i.EntryType = 'W' OR i.EntryType = 'S' ) 
 AND i.Complete = 'Y' AND i.GtrReference >= '' 
 and i.Line = '1'
 -- 同步修改：日期类型比较
 and i.DateCompleted >= DATEFROMPARTS(YEAR(DATEADD(day, -90, GETDATE())), MONTH(DATEADD(day, -90, GETDATE())), 1)
ORDER BY i.DateCompleted desc

兼容低版本SQL Server的方案 #

如果你的SQL Server版本低于2012，无法使用DATEFROMPARTS，可以用字符串拼接后转日期的方式：

-- 子查询中DateCompleted的替换写法
CAST(concat(d.TransfCompYear, '-', right('0' + d.TransfCompPeriod, 2), '-01') as date) as DateCompleted

-- WHERE子句条件的替换写法
and i.DateCompleted >= CAST(concat(left(convert(varchar(6),getdate()-90,112),4), '-', right(convert(varchar(6),getdate()-90,112),2), '-01') as date)

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