To get data for the last 5 days when date is stored as int in julian format

Question

I'm new to sql and need help on a query.

Database is JDEdwards In my database i have a column 'jdt' with Julian date stored as integer. Now i have to write a query to extract last 5 days data from the present day. My thinking approach is to convert current current date to Julian date as int and then juliandate-5

For example: Julian date=117209 i.e 2017-07-28 last 5 days date i.e 2017-07-23 -> 117204

I also have to do this in single query, i cannot add columns to existing table. And also this query needs t be generalized for every date i.e if i run this query it should automatically take that date of run.

Is my approach correct if yes, Please help me on the query. If there is a better approach please advise on this

TIA

Answer 1

"Julian" dates are the number of days since a specific date.

JDE uses a compressed date format you would have to convert to an actual date, so you cannot do integer math on a JDE date: All versions of JDE use a Julian Date format as follows: CYYDDD where C = Century; YY = a 2 digit year; and DD = the 3 digit number representing the day of the year

To convert the JDE date to an actual date:

Declare @jdedate int;
SET @jdedate = 117209

SELECT DATEADD(DAY,(@jdedate%1000)-1, DATEADD(YEAR,(@jdedate/1000),'1900-01-01'))

To convert current date to JDE date:

SELECT (DATEPART(YEAR, GETDATE())-1900) * 1000 + DATEPART(dayofyear,GETDATE())

Answer 2

I saw this question sitting out there, and thought I might add some information. I use Oracle for our JDE system, and I have always converted the dates in the following manner:

 TO_DATE(TO_CHAR(WMTRDJ+1900000),'YYYYDDD') AS "Date - Order/Transaction Date",

The column in this case is WMTRDJ and will convert the Julian date to the standard Gregorian date.

Once converted in the SELECT you can use a standard date comparison in your query. Like this (based on your default NLS_DATE_FORMAT):

 WHERE TO_DATE(TO_CHAR(WMTRDJ+1900000),'YYYYDDD') BETWEEN '5-JAN-2012' AND '6-JAN-2012'

I hope this helps!