JDE Date to Standard Date Format

[#1]

How do you convert a JDE date (CYYDDD) into MM/DD/YY?

[Matt45]

My initial hunch was right. The Julian dates I have from my data source is the Unix variant, meaning the starting reference date is actually 1970-01-01. The conversion of the date to seconds since the reference date is (JD − 2440587.5) × 86400.

To convert this to a date, I converted to seconds, converted to days, then added the days to 1970-01-01 within the date function to get my date in the data load editor:

My_Table:
LOAD
    OBJECTID,
    ([DISCOVERY_DATE]- 2440587.5)*86400 AS SecondsNum,
FROM "Table";

tJulian_Dates:
LOAD 
    [OBJECTID],
    [DISCOVERY_DATE] as JDE,
    SecondsNum/86400 as DaysNum
RESIDENT Table;

Julian_Dates:
LOAD 
    *,
    Date('1970-01-01' + DaysNum) as MY_DATE,
RESIDENT tJulian_Dates;
DROP TABLE tJulian_Dates;