0

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

mounika
  • 1
  • 2

2 Answers2

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())
Tom Phillips
  • 48
  • 3
  • Getting the error while using getdate() in JDEdwards Error: [SQL0204] GETDATE in *LIBL type *N not found. SQLState: 42704 ErrorCode: -204 – mounika Aug 20 '18 at 15:50
  • Thanku Tom for the conversion query. But may i know how can i select data from table for last 5 days based on a column which is julian date – mounika Aug 20 '18 at 15:53
  • i have to write this query on JDEdwards system. Following query worked select CAST(CASE WHEN SUBSTRING(TO_CHAR(current_timestamp, 'yyyy'), 1, 1) = '2' THEN '1' ELSE '0' END || TO_CHAR(current_timestamp, 'yyddd') AS INT). – mounika Sep 27 '18 at 22:01
  • But how can i retrieve only the data from past 5 days and this job occurs for every 3 days. The query should be written on JDE systems and seems like most date parameter doesn't seem to work. Please advise on this as there is a deadline coming up for me to submit this TIA – mounika Sep 27 '18 at 22:05
0

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!

dgibbons82
  • 128
  • 1
  • 10