SQL 将 JDE Julian 日期转换为 Gregorian

Question

提问by ShadowScripter

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.

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

Here's the function I came up with, which I simply call "ToGregorian"

这是我想出的函数，我简单地称之为“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

Takes a "julian" string.
Takes the first letter and adds it to century, starting from 19th.
Adds decade and years from the next 2 characters.
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)
Result ex: 111186=> 2011-07-05 00:00:00.000

需要一个“朱利安”字符串。
取第一个字母并将其添加到世纪，从 19 日开始。
从接下来的 2 个字符添加十年和年。
最后添加天数，即最后 3 个字符，并减去 1，因为它在第一个设置中已经有 1 天了。（例如 2011-01-01）
结果例如：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)

Answer 1

回答by Aaron Bertrand

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));

Result:

结果：

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

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 0instead of some string representation, which would cause determinism issues preventing the column from being persisted and potentially indexed).

假设此数据不经常更改，将日期实际存储为计算列可能会更有效（这就是为什么我选择基日期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;

Results:

结果：

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...

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

Answer 2

回答by David O'Rourke

The accepted answer is incorrect. It will fail to give the correct answer for 116060 which should be 29th February 2016. Instead it returns 1st March 2016.

接受的答案是不正确的。它将无法给出 116060 的正确答案，这应该是 2016 年 2 月 29 日。而是返回 2016 年 3 月 1 日。

JDE seems to store dates as integers, so rather than converting from strings I always go direct from the integer:

JDE 似乎将日期存储为整数，所以我总是直接从整数转换，而不是从字符串转换：

DATEADD(DAY, @Julian % 1000, DATEADD(YEAR, @Julian / 1000, '31-dec-1899'))

To go from a varchar(6) I use:

从 varchar(6) 开始，我使用：

DATEADD(DAY, CAST(RIGHT(@Julian,3) AS int), DATEADD(YEAR, CAST(LEFT(@Julian,LEN(@Julian)-3) AS int), '31-dec-1899'))

Answer 3

回答by user5682691

DATE(CHAR(1900000 + GLDGJ)) where GLDGJ is the Julian date value

DATE(CHAR(1900000 + GLDGJ)) 其中 GLDGJ 是儒略日期值

Answer 4

回答by HalMcGee

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[ToGregorian]    Script Date: 08/18/2015 14:33:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ToGregorian](@julian varchar(6),@time varchar(6))
RETURNS datetime 
AS 
BEGIN
    DECLARE @datetime datetime,@hour int, @minute int, @second int

    set @time = ltrim(rtrim(@time));
    set @julian = ltrim(rtrim(@julian));

    if(LEN(@julian) = 5)
        set @julian = '0' + @julian


    IF(LEN(@time) = 6)
        BEGIN
            SET @hour = Convert(int,LEFT(@time,2));
            SET @minute = CONVERT(int,Substring(@time,3,2));
            SET @second = CONVERT(int,Substring(@time,5,2));
        END
    else IF(LEN(@time) = 5)
        BEGIN
            SET @hour = Convert(int,LEFT(@time,1));
            SET @minute = CONVERT(int,Substring(@time,2,2));
            SET @second = CONVERT(int,Substring(@time,4,2));
        END
    else IF(LEN(@time) = 4)
        BEGIN
            SET @hour = 0;
            SET @minute = CONVERT(int,LEFT(@time,2));
            SET @second = CONVERT(int,Substring(@time,3,2));
        END
    else IF(LEN(@time) = 3)
        BEGIN
            SET @hour = 0;
            SET @minute = CONVERT(int,LEFT(@time,1));
            SET @second = CONVERT(int,Substring(@time,2,2));
        END
    else
        BEGIN
            SET @hour = 0;
            SET @minute = 0;
            SET @second = @time;
        END

    SET @datetime = DATEADD(YEAR,100*CONVERT(INT, LEFT(@julian,1))+10*CONVERT(INT, SUBSTRING(@julian, 2,1))+CONVERT(INT, SUBSTRING(@julian,3,1)),0);                     
    SET @datetime = DATEADD(DAY, CONVERT(INT,SUBSTRING(@julian, 4, 3))-1,@datetime);                   
    SET @datetime = DATEADD(hour,@hour,@datetime)
    SET @datetime = DATEADD(minute,@minute,@datetime);
    SET @datetime = DATEADD(second,@second,@datetime);

    RETURN @datetime
END

Answer 5

回答by Bob Sterzenbach

I don't think anyone has mentioned it, but JDE has a table just for this.

我认为没有人提到过它，但 JDE 有一个专门用于此的表。

It's the F00365 data table. As far as I know, it's a translation table just for this issue.

这是F00365数据表。据我所知，这是一个专门针对这个问题的翻译表。

To get a Gregorian date, you join the F00365 table using the ONDTEJ field (which is the Julian date),and you return the ONDATE value, which is Gregorian. e.g.

要获得公历日期，请使用 ONDTEJ 字段（即儒略日期）连接 F00365 表，并返回 ONDATE 值，即公历。例如

SELECT 
    DateReq.ONDATE 
FROM F00101 NamesData 
INNER JOIN F00365 DateReq 
    ON DateReq.ONDTEJ = NamesData.ABUPMJ

No math required. No weird issues with leap years.

不需要数学。闰年没有奇怪的问题。

SQL 将 JDE Julian 日期转换为 Gregorian

提问by ShadowScripter

回答by Aaron Bertrand

回答by David O'Rourke

回答by user5682691

回答by HalMcGee

回答by Bob Sterzenbach

相关推荐

最近更新

标签

SQL 将 JDE Julian 日期转换为 Gregorian

提问by ShadowScripter

回答by Aaron Bertrand

回答by David O'Rourke

回答by user5682691

回答by HalMcGee

回答by Bob Sterzenbach

相关推荐

SQL 选择前 150 行，然后选择接下来的 150 行，依此类推？

SQL 比较和空值的问题

Oracle SQL 更新查询仅更新值为空的值

SQL 如何创建引用数据类型表列的 Oracle 类型？

相关推荐

最近更新

标签