如何计算客户销售日期平均间隔？解决Shamsi日期转换问题

问题核心 #

要从sle.tblSaleHdr表计算各客户的销售日期平均间隔，但SaleDate字段存储的是波斯历（Shamsi）字符串，使用gnr.solartodatetime转换时返回NULL，触发“字符转日期失败”报错，导致后续日期计算无法进行。

解决方案 #

1. 先排查波斯历日期的格式问题 #

首先确认SaleDate的字符串格式是否统一，有没有混杂多种格式（比如yyyy/mm/dd、yyyy-mm-dd、yyyymmdd）：

SELECT DISTINCT SaleDate, LEN(SaleDate)
FROM sle.tblSaleHdr
WHERE SaleDate IS NOT NULL

格式不统一是函数转换失败的常见原因，先把格式统一再处理。

2. 手动解析波斯历转公历（兼容多数格式） #

如果gnr.solartodatetime函数无法适配当前格式，可以手动拆分日期字段，用波斯历转公历的通用公式计算（以yyyy/mm/dd格式为例）：

WITH SaleDateSplit AS (
    SELECT 
        CustomerID,
        SaleDate,
        -- 拆分波斯历的年、月、日
        CAST(SUBSTRING(SaleDate, 1, 4) AS INT) AS ShamsiY,
        CAST(SUBSTRING(SaleDate, 6, 2) AS INT) AS ShamsiM,
        CAST(SUBSTRING(SaleDate, 9, 2) AS INT) AS ShamsiD
    FROM sle.tblSaleHdr
    WHERE SaleDate IS NOT NULL
),
GregorianConverted AS (
    SELECT 
        CustomerID,
        -- 波斯历转公历的计算逻辑（适用于1300-1499年的波斯历）
        DATEADD(DAY, 
            ShamsiD - 1 + 
            CASE ShamsiM
                WHEN 1 THEN 0 WHEN 2 THEN 31 WHEN 3 THEN 62 WHEN 4 THEN 93
                WHEN 5 THEN 124 WHEN 6 THEN 155 WHEN 7 THEN 186 WHEN 8 THEN 216
                WHEN 9 THEN 246 WHEN 10 THEN 276 WHEN 11 THEN 306 WHEN 12 THEN 336
            END +
            (ShamsiY - 1300) * 365 + FLOOR((ShamsiY - 1300)/4) + 226899,
            '1900-01-01'
        ) AS SaleDateGregorian
    FROM SaleDateSplit
),
SalesIntervals AS (
    SELECT 
        CustomerID,
        DATEDIFF(DAY, 
            LAG(SaleDateGregorian) OVER (PARTITION BY CustomerID ORDER BY SaleDateGregorian),
            SaleDateGregorian
        ) AS DaysBetween
    FROM GregorianConverted
)
-- 计算各客户的平均销售间隔
SELECT 
    CustomerID,
    AVG(CAST(DaysBetween AS FLOAT)) AS AvgSalesIntervalDays
FROM SalesIntervals
WHERE DaysBetween IS NOT NULL
GROUP BY CustomerID

3. 适配gnr.solartodatetime函数的输入格式 #

如果函数只接受特定格式（比如无分隔符的yyyymmdd），先转换字符串格式再调用：

SELECT 
    CustomerID,
    AVG(DATEDIFF(DAY, 
        LAG(gnr.solartodatetime(REPLACE(SaleDate, '/', ''))) OVER (PARTITION BY CustomerID ORDER BY gnr.solartodatetime(REPLACE(SaleDate, '/', ''))),
        gnr.solartodatetime(REPLACE(SaleDate, '/', ''))
    )) AS AvgSalesIntervalDays
FROM sle.tblSaleHdr
WHERE SaleDate IS NOT NULL
GROUP BY CustomerID

如果原格式是yyyy-mm-dd，把REPLACE(SaleDate, '/', '')改成REPLACE(SaleDate, '-', '')即可。

4. 过滤无效的波斯历日期 #

如果存在无效日期（比如1402/13/32），先过滤掉这些数据避免转换出错：

SELECT SaleDate
FROM sle.tblSaleHdr
WHERE 
    CAST(SUBSTRING(SaleDate, 6, 2) AS INT) NOT BETWEEN 1 AND 12
    OR CAST(SUBSTRING(SaleDate, 9, 2) AS INT) NOT BETWEEN 1 AND 31
    -- 可补充闰年判断：波斯历闰年时第12月有30天，平年29天，根据需要添加

验证转换结果 #

无论用哪种方法，先抽10条数据验证转换后的公历日期是否正确：

SELECT 
    SaleDate,
    DATEADD(DAY, 
        CAST(SUBSTRING(SaleDate, 9, 2) AS INT) -1 + 
        CASE CAST(SUBSTRING(SaleDate,6,2) AS INT)
            WHEN 1 THEN 0 WHEN 2 THEN31 WHEN3 THEN62 WHEN4 THEN93
            WHEN5 THEN124 WHEN6 THEN155 WHEN7 THEN186 WHEN8 THEN216
            WHEN9 THEN246 WHEN10 THEN276 WHEN11 THEN306 WHEN12 THEN336
        END +
        (CAST(SUBSTRING(SaleDate,1,4) AS INT)-1300)*365 + FLOOR((CAST(SUBSTRING(SaleDate,1,4) AS INT)-1300)/4)+226899,
        '1900-01-01'
    ) AS ConvertedGregorianDate
FROM sle.tblSaleHdr
WHERE SaleDate IS NOT NULL
LIMIT 10

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