SQL 查询 - 使用转换/格式化日期

Question

当我尝试使用以下语句将当前AgreementEndDate转换为 2 位数字年份时，出现以下错误。

CONVERT(VARCHAR(2), (FORMAT(b.AgreementEndDate, 'yy') - 1))

参数

数据类型 char 对于格式函数的参数 1 无效

感谢您的帮助

WITH LicenseAgreement AS
(
    SELECT 
        MV.ID, AM.AgreementID, AM.AgreementEffectiveDate, AM.AgreementEndDate
    FROM 
        [dbo].[Agreement] A
    WHERE
        A.AgreementStatusName = 'Active'
) 
SELECT
    LA.ID, 
    LA.AgreementID,
    LA.AgreementEffectiveDate,
    LA.AgreementEndDate,
    MIN(CASE WHEN LA.AgreementEndDate >= CONVERT(date, CURRENT_TIMESTAMP) AND ((MONTH(LA.AgreementEndDate) >= 7 AND MONTH(LA.AgreementEndDate) <= 12)) THEN 'FY' + CONVERT(VARCHAR(2), (FORMAT(AgreementEndDate, 'yy') - 1)) + ' H1' 
            WHEN LA.AgreementEndDate >= CONVERT(date, CURRENT_TIMESTAMP)  AND ((month(LA.AgreementEndDate) >= 1 AND month(LA.AgreementEndDate) <= 6)) THEN 'FY' + convert(VARCHAR(2), (FORMAT(AgreementEndDate, 'yy') - 1)) + ' H2' 
            ELSE NULL 
        END) AS 'Agreement Window'
FROM
    LicenseAgreement LA
GROUP BY
    ID, AgreementID, AgreementEndDate, AgreementEffectiveDate

Answer 1

尝试使用它而不是转换，因为您只需要年份

RIGHT(YEAR(b.AgreementEndDate), 2)