In Excel replace [IXEFFF] with the cell reference for the JDE Julian. This
formula is from Access.
=DateValue("12/31/" &
(Int([IXEFFF]/1000))+1899)+([ixefff]-Int([ixefff]/1000)*1000)
Hal McGee
Engineering Data Manager
Group Engineering - Process and Compliance
Seating Products Group
B/E Aerospace
Winston-Salem, NC
miaconis
<miaconis@soartec
hsolutions.com> To
Sent by:
[email protected]
jdeworld-bounces@ cc
jdelist.com
Subject
RE: Julian to Gregorian date
09/27/2005 06:28 conversion in EXCEL
AM
Please respond to
JD Edwards® World
<jdeworld@jdelist
.com>
I found a function from one of my other email lists a couple of years ago.
I'll try to cut and paste the info here.
Convert J.D. Edwards Dates to Other Formats
Hey, Howard:
I want to convert a J.D. Edwards date, (CYYDDD where C represents the
century, YY represents the year, and DDD represents the day of the year) to
a more readable format, (like YYYY-MM-DD). I can do this in Crystal
Reports,
but is there a way to do it in SQL?
-- Domenic
This is a good one, Domenic, as I first thought it was impossible, but on
further reading of the IBM documentation, I found a
little known use for the DATE function. I found that DATE can take a
variety
of arguments. As I was reading the documentation on DATE, I noticed that
one
of the acceptable date formats is a seven-character string in the form
YYYYNNN where YYYY is the year and NNN is the day number. When passed !
this
information, DATE will return a date data type value representative of the
string that was passed in.
To take advantage of this feature, we need only to convert your
DECIMAL(6,0)
representation of the date into the required CHAR(7) representation and
pass
it to the date function. Let's do it!
First we create some test data:
CREATE TABLE mceis.t1 (d DECIMAL(6,0))
INSERT INTO mceis.t1 VALUES (099001)
INSERT INTO mceis.t1 VALUES (100001)
INSERT INTO mceis.t1 VALUES (101001)
INSERT INTO mceis.t1 VALUES (099034)
INSERT INTO mceis.t1 VALUES (100066)
INSERT INTO mceis.t1 VALUES (101098)
INSERT INTO mceis.t1 VALUES (101198)
INSERT INTO mceis.t1 VALUES (101298)
INSERT INTO mceis.t1 VALUES (101365)
Notice that I used the SQL naming convention. The collection and table
names
are separated by a period (.). Be sure to use a forward slash (/) instead
of
a period if you're using SYS naming convention.
! OK, now that we have some data, let's work on how to make the string we
need. The first step is to convert the CYY representation of the year to a
four-digit year:
DECIMAL(d +1900000,7,0))
The next step is to convert the seven-digit decimal number to a character
string. The DIGITS function does this nicely:
DIGITS(DECIMAL(d +1900000,7,0)))
Here is an example statement using the transformation and the resulting
data:
SELECT d, DATE(DIGITS( DECIMAL(d +1900000,7,0)) ) AS CONVDATE
FROM mceis.t1
R#
D
CONVDATE
1
99001
01/01/1999
2
100001
01/01/2000
3
101001
01/01/2001
4
99034
02/03/1999
5
100066
03/06/2000
6
101098
04/08/2001
7
101365
12/31/2001
Now, let's use the formula in a common table expression and then use the
resultin! g field in a number of expressions:
SELECT D, CONVDATE,
DAYOFWEEK(CONVDATE) AS DAYOFWEEK,
QUARTER(CONVDATE) AS QUARTER,
CONVDATE - 31 days AS DMINUS31,
MONTH(CONVDATE) AS MONTH,
days(CURRENT_DATE)-days(CONVDATE) AS ELAPSED_DAYS
FROM (SELECT d, DATE(DIGITS( DECIMAL(d +1900000,7,0))) AS CONVDATE
FROM mceis.t1) AS X
Notice that an SQL statement is nested within a FROM clause. The inner SQL
statement runs first; yielding a temporary table I call X. The outer SELECT
then runs against this temporary table.
The above statement would yield the following result table from the sample
data:
R#
D
CONVDATE
DAYOFWEEK
QUARTER
DMINUS31
MONTH
ELAPSED_DAYS
1
99001
01/01/1999
6
1
12/01/1998
1
1330
2
100001
01/01/2000
7
1
12/01/1999 !
1
965
3
101001
01/01/2001
2
1
12/01/2000
1
599
4
99034
02/03/1999
4
1
01/03/1999
2
1297
5
100066
03/06/2000
2
1
02/04/2000
3
900
6
101098
04/08/2001
1
2
03/08/2001
4
502
7
101365
12/31/2001
2
4
11/30/2001
12
235
So, using the date data type can open up a world of calculations against
your data. I hope this helps.
-- Howard
Howard F. Arner, Jr. is a writer and consultant for Client Server
Development, Inc . You can purchase Howard's
book, "iSeries and AS/400 SQL at Work," from
www.sqlthing.com/books or
visit
www.sqlthing.com to find out more about! manipulating dates on the AS/400.
_____
From:
[email protected] [mailto:
[email protected]] On
Behalf Of sally_earnest
Sent: Monday, September 26, 2005 3:49 PM
To:
[email protected]
Subject: Re: Julian to Gregorian date conversion in EXCEL
Any SQL magic for Gregorian (specifically today's date) converted to the
JDE
Julian CYYDDD?
Mohawk Industries, Inc. JDE A7.3 C14, AP,GL,AP,PO,IN...
_____
The entire JDELIST
thread is available for viewing.
Looking for a job? Check out the Job
forum
This is the JDELIST World Mailing List.
The instructions on how to unsubscribe from any JDELIST mailing list are
available here .
JDELIST is not affiliated with JDEdwardsR.
.