JD EDWARDS (AS/400 software) uses dates called JULIAN.
A JULIAN Date identifies the year first, then the number of days into the year that this
date appears. So, Jan 15, 1999 is 99015. March 15 is 99074. March 15, 2001 is 101074 Dates
in Y2K start with 100 (2000), 101(2001) etc.
CONVERT JULIAN DATE TO DATE USING AS/400 SQL
Using AS/400 SQL, you need to perform a few tricks to convert to an AS/400 date.
In the following example, sdtrdj is the "Date Entered" field in the open
orders file (JDE F4211). We'll take that and convert it into a date string.Use this
formula for ODBC-initiated SQL strings or in AS/400 programming.
The pipe symbol ( || ) can also be substitued with CONCAT( )
=======================
select sdtrdj,date('01/01/' ||
substr(strip(char(int(sdtrdj/1000))),
length(strip(char(int(sdtrdj/1000))))-1))
+(sdtrdj-(int(sdtrdj/1000)*1000)-1) days from f4211
========================
The first few lines, up to "-1))" convert the julian year value to a string,
like '99' or '01'. The length() function is necessary because there's no RIGHT() function
in AS/400 SQL, so you have to calculate how many digits there are in the years value
(either three for Y2K years, or 2 for <Y2K years). Subtracting 1 tells the substr()
function where to start.
The second part, after the "+" sign, adds the number of days to that formula
from the first day of that year. Subtract 1 because you REALLY want to start on Dec 31, so
subtract 1 from date(01/01/xx)
Back to Mini Solve Products and other Tips
|
