将 JDE 儒略日期转换为公历

Question

我正在尝试转换 JDE 日期，并积累了大量信息，并认为我会尝试做一个 SQL 转换函数来简化一些任务。

这是我想出的函数，我简称为“ToGregorian”

 CREATE FUNCTION [dbo].[ToGregorian](@julian varchar(6))
RETURNS datetime AS BEGIN
    DECLARE @datetime datetime

    SET @datetime = CAST(19+CAST(SUBSTRING(@julian, 1, 1) as int) as varchar(4))+SUBSTRING(@julian, 2,2)+'-01-01'
    SET @datetime = DATEADD(day, CAST(SUBSTRING(@julian, 4,3) as int)-1, @datetime)

    RETURN @datetime
END

1. 采用“朱利安”字符串。
2. 取第一个字母并将其添加到世纪，从 19 日开始。
3. 从接下来的 2 个字符开始添加十年和年。
4. 最后添加天数，即最后 3 个字符，并减去 1，因为它在第一个设置中已经有 1 天。 （例如 2011-01-01）
5. 结果前： 111186 => 2011-07-05 00:00:00.000

在我看来，这有点笨拙和矫枉过正，我希望有更好的方法来做到这一点。也许我进行了太多转换，或者我应该一起使用不同的方法？

任何建议如何改进功能？

也许是一种不同的、更好的方法？

不介意它是否也可以更具可读性…

我也有一个内联版本，例如，如果我只有读取权限并且不能使用函数，这看起来也很乱，是否可以使其更具可读性或更好？

 CAST(REPLACE(Convert(VARCHAR, DATEADD(d,CAST(SUBSTRING(CAST([column] AS VARCHAR), 4,3) AS INT)-1, CAST(CAST(19+CAST(SUBSTRING(CAST([column] AS VARCHAR), 1,1) AS INT) AS VARCHAR)+SUBSTRING(CAST([column] AS VARCHAR), 2,2) + '-01-01' AS DATETIME)), 111), '/', '-') AS DATETIME)

原文由 ShadowScripter 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

我认为使用本机日期时间数学比所有这些来回切换到各种字符串、日期和数字格式更有效。

 DECLARE @julian VARCHAR(6) = '111186';

SELECT DATEADD(YEAR,
  100*CONVERT(INT, LEFT(@julian,1))
  +10*CONVERT(INT, SUBSTRING(@julian, 2,1))
  +CONVERT(INT, SUBSTRING(@julian,3,1)),
 DATEADD(DAY, CONVERT(INT,SUBSTRING(@julian, 4, 3))-1,
 0));

结果：

 ===================
2011-07-05 00:00:00

假设这些数据不经常更改，将日期实际存储为计算列可能会更有效（这就是为什么我选择基准日期 0 而不是一些字符串表示，这会导致确定性问题阻止列被持久化并可能被索引）。

 CREATE TABLE dbo.JDEDates
(
    JDEDate VARCHAR(6),

    GregorianDate AS CONVERT(SMALLDATETIME,
      DATEADD(YEAR,
        100*CONVERT(INT, LEFT(RIGHT('0'+JDEDate,6),1))
        +10*CONVERT(INT, SUBSTRING(RIGHT('0'+JDEDate,6), 2,1))
        +CONVERT(INT, SUBSTRING(RIGHT('0'+JDEDate,6),3,1)),
      DATEADD(DAY, CONVERT(INT, RIGHT(JDEDate, 3))-1,
      0))
    ) PERSISTED
);

INSERT dbo.JDEDates(JDEDate) SELECT '111186';

SELECT JDEDate, GregorianDate FROM dbo.JDEDates;

结果：

 JDEDate GregorianDate
======= ===================
111186  2011-07-05 00:00:00

即使您不索引该列，它仍然会隐藏丑陋的计算对您来说，被持久化您只需在写入时支付，因为它不会导致您在查询时执行昂贵的功能操作，只要该列被引用…

原文由 Aaron Bertrand 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

要将儒略日期转换为公历：

 DATEADD(DAY, @julian % 1000 - 1, DATEADD(YEAR, @julian / 1000, 0))

要将公历日期转换为儒略日期：

 (YEAR(@date) - 1900) * 1000 + DATEPART(DAYOFYEAR, @date)

试试看：

 DECLARE @julian VARCHAR(6);
SET @julian = N'122129';
SELECT @julian [JulianDate],
       DATEADD(YEAR, @julian / 1000, 0) [Year],
       @julian % 1000 [DayOfYear],
       DATEADD(DAY, @julian % 1000 - 1, DATEADD(YEAR, @julian / 1000, 0)) [Date];

DECLARE @george DATETIME;
SET @george = '2022-5-9';
SELECT @george [Date],
       YEAR(@george) [Year],
       DATEPART(DAYOFYEAR, @george) [DayOfYear],
       (YEAR(@george) - 1900) * 1000 + DATEPART(DAYOFYEAR, @george) [JulianDate];

原文由 walterhuang 发布，翻译遵循 CC BY-SA 4.0 许可协议