JD Edwards Date Conversion

[skippaNH]

Does anyone know how to perform a conversion in the JD Edwards "Date" format by using a SQL View? The format is actually a 9 digit numeric. It is not stored in date format. It is CYYddd.

C - Century
YY - Year
Ddd - day of year


Century - 0 = 1900
Century - 1 = 2000
YY = 01, 02, 67, 95, etc.
DDD = 1-365 to represent the day.

We are trying to convert this on the SQL side to pull it into Cognos Impromptu Report Writer.

 

[ashorey]

I use this to convert JDE Julian dates,

DECLARE @TEST AS VARCHAR(6)
SELECT @TEST='102312'

SELECT DATEADD(DAY, CAST(SUBSTRING(@TEST, (LEN(@TEST)-2) ,3) AS INT)-1, CAST((CAST(CAST(ISNULL(SUBSTRING(@TEST, (LEN(@TEST)-5) ,1),0) AS INT) + 19 AS VARCHAR(4))+ SUBSTRING(@TEST, (LEN(@TEST)-4) ,2))+'-01-01'AS SMALLDATETIME))

Just replace the Variable with the field name.

The above is easier read as:-

--HAS TO BE CHAR TO KEEP LEADING ZERO
DECLARE @TEST AS VARCHAR(6)
DECLARE @DA AS CHAR(3)
DECLARE @YR AS CHAR(2)
DECLARE @CE AS CHAR(1)
DECLARE @vYEAR AS CHAR(4)
SELECT @TEST='102312'
--GET DAY OF YEAR
SELECT @DA = SUBSTRING(@TEST, (LEN(@TEST)-2) ,3)

--GET YEAR
SELECT @YR = SUBSTRING(@TEST, (LEN(@TEST)-4) ,2)

--GET CENTURY
SELECT @CE = SUBSTRING(@TEST, (LEN(@TEST)-5) ,1)

--CONVERT CENTURY TO SOMTHING MEANINGFUL
SELECT @vYEAR = (CAST(CAST(ISNULL(@CE,0) AS INT) + 19 AS VARCHAR(4))+ @YR)

--USE CENTURY AND YEAR AND DATEADD TO GET THE DATE.
SELECT DATEADD(DAY, CAST(@da AS INT)-1, CAST(@vYear+'-01-01'AS SMALLDATETIME))

The other way is to use table F00365 in JDE, it just holds date conversions, run DW P00365, and set the date ranges you require, you can then join onto the table.


Andy

 

[MeanGreen]

I created this SQL query that would give you your date with one pass on multiple jde dates in a table:

select jdedate
,dateadd(dd,convert(integer,substring(jdedate,4,6)),convert(datetime,case(left(jdedate,1))
when '0' then '19'
else '20'
end + substring(jdedate,2,2) + '/1/1'))
from jde_table

Hope this helps.