Converting JDE Julian date to Gregorian

Question

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.

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

Answer 1

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.

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

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

To go from a varchar(6) I use:

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

Answer 2

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(DAY, SUBSTRING(@julian,4,3)-1,
  DATEADD(YEAR, 100 * LEFT(@julian,1) 
          + 10 * SUBSTRING(@julian,2,1)
               + SUBSTRING(@julian,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 0 instead of some string representation, which would cause determinism issues preventing the column from being persisted and potentially indexed). 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...

• Corrected for leap year

Answer 3

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 4

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

Answer 5

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

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

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.

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

No math required. No weird issues with leap years.

Answer 6

To convert Julian date to Gregorian:

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

To convert Gregorian to Julian date:

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

Try it:

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

Answer 7

Here a formula that can be used I have looking all over different site and I had even though I had to twick a bit has work well for me and you do not need to create any special function stuff:

DATEADD(DAY,CONVERT(INT,SUBSTRING(CONVERT(CHAR,(JULIANDDATEFIELD + 1900000)),5,3))-1,DATEFROMPARTS(SUBSTRING(CONVERT(CHAR,(JULIANDDATEFIELD + 1900000)),1,4),01,01))