将JDE Julian日期转换为Gregorian [英] Converting JDE Julian date to Gregorian

问题描述

我正在尝试转换 JDE日期，以及已经收集了大量信息，并且认为我会尝试执行SQL转换功能来简化一些任务。

I'm trying to convert JDE dates, and have amassed a large quantity of information and figured I'd try to do an SQL conversion function to simplify some tasks.

这是我想出的功能，我简单调用ToGregorian

Here's the function I came up with, which I simply call "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

1. Takes a "julian" string.
2. Takes the first letter and adds it to century, starting from 19th.
3. Adds decade and years from the next 2 characters.
4. Finally adds the days, which are the final 3 characters, and subtracts 1 as it already had 1 day in the first setup. (eg. 2011-01-01)
5. Result ex: 111186 => 2011-07-05 00:00:00.000

在我看来，这有点笨拙，希望有更好的方法来做到这一点。也许我做了太多的转换，也许我应该使用一种不同的方法？

In my opinion this is a bit clumsy and overkill, and I'm hoping there is a better way of doing this. Perhaps I'm doing too many conversions or maybe I should use a different method alltogether?

有什么建议如何改进功能？

也许一个不同，更好的方法？

不介意，如果它可以更可读...

Any advice how to improve the function?
Perhaps a different, better, method?
Wouldn't mind if it could be more readable as well...

我也有一个内联版本，其中如果是，我只有读权限，不能使用功能，这也看起来很乱，可以使它更可读或更好吗？

I've also got an inline version, where if for instance, I only have read privileges and can't use functions, which also looks messy, is it possible to make it more readable, or better?

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)

推荐答案

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

I think it is more efficient to use native datetime math than all this switching back and forth to various string, date and numeric formats.

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 ，而不是一些字符串表示，这将导致确定性问题阻止列被持久化并可能编入索引）。

Assuming this data doesn't change often, it may be much more efficient to actually store the date as a computed column (which is why I chose the base date of 0 instead of some string representation, which would cause determinism issues preventing the column from being persisted and potentially indexed).

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

即使你没有对列进行索引，它仍然隐藏了你的丑陋的计算持续存在，您只需在写入时支付，因为它不会导致您在查询时执行昂贵的功能操作，只要该列被引用...

Even if you don't index the column, it still hides the ugly calculation away from you, being persisted you only pay that at write time, as it doesn't cause you to perform expensive functional operations at query time whenever that column is referenced...

这篇关于将JDE Julian日期转换为Gregorian的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！