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

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

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