Julian date conversion -

Bill Meyer

Active Member
Perhaps someone can help. .I am using As400 Query on File F4111. The dates eventually downloaded to Excel are in Julian date format. Any ideas on how to convert Julian dates to regular date formats.
 
And that's what you will get if you don't use something of JDE's. If you are using World, use the World Writer report writer to get what you are looking for. Otherwise, you probably need to populate the F00365 file (see P00365) and join to it to get date formatting the way you want it.

Bill Meyer <[email protected]> wrote:perhaps someone can help. .I am using As400 Query on File F4111. The dates eventually downloaded to Excel are in Julian date format. Any ideas on how to convert Julian dates to regular date formats.
 
There are ways you can manipulate the date in Query, but they are a pain and
you have to remember them each time. The easiest way we found was to create
a table with the last five years and a few years in the future of about
every other data format imaginable, including the JDE-style Julian Data.
Then you can link to that table in your queries. Here is our file format.



Data Field Buffer Buffer Field Column

Field Type Length Length Position Usage Heading

JULDT ZONED 6 0 6 1 Both JULDT

Field text . . . . . . . . . . . . . . . : CYYDDD

MDYDT ZONED 6 0 6 7 Both MDYDT

Field text . . . . . . . . . . . . . . . : MMDDYY

MDYFMT CHAR 8 8 13 Both MDYFMT

Field text . . . . . . . . . . . . . . . : Formated MM/DD/YY

Coded Character Set Identifier . . . . . : 37

YMDDT ZONED 6 0 6 21 Both YMDDT

Field text . . . . . . . . . . . . . . . : YYMMDD

YMDFMT CHAR 8 8 27 Both YMDFMT

Field text . . . . . . . . . . . . . . . : Formated YY/MM/DD

Coded Character Set Identifier . . . . . : 37

DMYDT ZONED 6 0 6 35 Both DMYDT

Field text . . . . . . . . . . . . . . . : DDMMYY

DMYFMT CHAR 8 8 41 Both DMYFMT

Field text . . . . . . . . . . . . . . . : Formated DD/MM/YY

Coded Character Set Identifier . . . . . : 37

ISODT ZONED 8 0 8 49 Both ISODT

Field text . . . . . . . . . . . . . . . : YYYYMMDD

ISOFMT CHAR 10 10 57 Both ISOFMT

Field text . . . . . . . . . . . . . . . : YYYY/MM/DD

Coded Character Set Identifier . . . . . : 37

USADT ZONED 8 0 8 67 Both USADT

Field text . . . . . . . . . . . . . . . : MMDDYYYY

USAFMT CHAR 10 10 75 Both USAFMT

Field text . . . . . . . . . . . . . . . : MM/DD/YYYY

Coded Character Set Identifier . . . . . : 37

EURDT ZONED 8 0 8 85 Both EURDT

Field text . . . . . . . . . . . . . . . : DDMMYYYY

EURFMT CHAR 10 10 93 Both EURFMT

Field text . . . . . . . . . . . . . . . : DD/MM/YYYY

Coded Character Set Identifier . . . . . : 37

JULLDT ZONED 7 0 7 103 Both JULLDT

Field text . . . . . . . . . . . . . . . : YYYYDDD

DYOFWKID ZONED 2 0 2 110 Both DYOFWKID

Field text . . . . . . . . . . . . . . . : Day of the Week Indicator

DYOFWKDS CHAR 9 9 112 Both DYOFWKDS

Field text . . . . . . . . . . . . . . . : Day of the Week Description

Coded Character Set Identifier . . . . . : 37

CYMNTH ZONED 6 0 6 121 Both CYMNTH

Field text . . . . . . . . . . . . . . . : Year and Month YYYYMM

MONLY ZONED 2 0 2 127 Both MONLY

Field text . . . . . . . . . . . . . . . : Month Only

DONLY ZONED 2 0 2 129 Both DONLY

Field text . . . . . . . . . . . . . . . : Day Only

CYONLY ZONED 4 0 4 131 Both CYONLY

Field text . . . . . . . . . . . . . . . : Year Only YYYY

YONLY ZONED 2 0 2 135 Both YONLY

Field text . . . . . . . . . . . . . . . : Year Only YY

CIND ZONED 1 0 1 137 Both CIND

Field text . . . . . . . . . . . . . . . : Century Indicator 0 or 1

CYIND ZONED 2 0 2 138 Both CYIND

Field text . . . . . . . . . . . . . . . : Century Code 19 or 20

LEAPYR CHAR 1 1 140 Both LEAPYR

Field text . . . . . . . . . . . . . . . : Leap Year Ind (Y N)

Coded Character Set Identifier . . . . . : 37

DYFORXLS ZONED 7 0 7 141 Both DYFORXLS

Field text . . . . . . . . . . . . . . . : Days within .xls




_____

From: [email protected] [mailto:[email protected]] On
Behalf Of Bill Meyer
Sent: Tuesday, February 01, 2005 8:33 AM
To: [email protected]
Subject: Julian date conversion -



Perhaps someone can help. .I am using As400 Query on File F4111. The dates
eventually downloaded to Excel are in Julian date format. Any ideas on how
to convert Julian dates to regular date formats.

_____


The entire <http://www.jdelist.com/ubb/showflat.php?Cat=&Board=> JDELIST
thread is available for viewing.


Looking for a job? Check out the Job
forum


This is the JDELIST EnterpriseOne and World Applications Mailing List.
The instructions on how to unsubscribe from any JDELIST mailing list are
available here <http://www.jdelist.com/unsubscr.shtml> .
JDELIST is not affiliated with JDEdwardsR.
 
Bill, this question came up a few years ago and someone smarter than I came up with this formula, which I used extensively:

date(substr(digits(xxxxxx+1900000),2,7)) Note: xxxxxx =3D date field name.

Regards,
Sally White
 
Bill -

One way is to use F00365 (there is a DW to load it with dates) and Join F4111 to F00365 using the Julian date (the key to F00365), and pull the other date formats from this file...

F00365 has the date several different ways:

1) separate fields for year, month, and day
2) Text field (i.e. - February 1, 2005)
3) System date (i.e. - 02/01/05)

Steve
 
Re: Julian date conversion - This works for me.

SELECT CASE WHEN RPDGJ != 0 THEN CONVERT(DATETIME, '12/31/'
+ LEFT(CAST(ROUND(RPDGJ / 1000 + 1899, 0) AS VARCHAR), 4))
+ 1000 * (RPDGJ / 1000 + 1900 - ROUND(RPDGJ / 1000 + 1900, 0))
ELSE NULL END AS [General Ledger Date],
CASE WHEN RPDGJ != 0 THEN MONTH(CONVERT(DATETIME, '12/31/'
+ LEFT(CAST(ROUND(RPDGJ / 1000 + 1899, 0) AS VARCHAR), 4))
+ 1000 * (RPDGJ / 1000 + 1900 - ROUND(RPDGJ / 1000 + 1900, 0)))
ELSE 0 END AS [Month],
CASE WHEN RPDGJ != 0 THEN DAY(CONVERT(DATETIME, '12/31/'
+ LEFT(CAST(ROUND(RPDGJ / 1000 + 1899, 0) AS VARCHAR), 4))
+ 1000 * (RPDGJ / 1000 + 1900 - ROUND(RPDGJ / 1000 + 1900, 0)))
ELSE 0 END AS [Day],
CASE WHEN RPDGJ != 0 THEN YEAR(CONVERT(DATETIME, '12/31/'
+ LEFT(CAST(ROUND(RPDGJ / 1000 + 1899, 0) AS VARCHAR), 4))
+ 1000 * (RPDGJ / 1000 + 1900 - ROUND(RPDGJ / 1000 + 1900, 0)))
ELSE 0 END AS [Year]
FROM PRODDTA.F0411
ORDER BY RPDGJ

You can simplify it by removing the CASE statements. I use them to avoid problems with missing dates. Obviously you will have to adjust your statement to comply with your particular dialect of SQL. This is Microsoft.
 
Thanks you all.
I was successful with Sally's routine. Much appreciated !
 
Re: Julian date conversion - Sally\'s solution in MS-SQL?

Has anyone translated Sally's function to MS-SQL? Its simplicity is beautiful.

This is the best I've been able to do with the CASE statement removed.

SELECT CONVERT(DATETIME, '12/31/' + LEFT(CAST(ROUND(RPDGJ / 1000 + 1899, 0) AS VARCHAR), 4))
+ 1000 * (RPDGJ / 1000 + 1900 - ROUND(RPDGJ / 1000 + 1900, 0)) AS [General Ledger Date]
FROM PRODDTA.F0411
ORDER BY RPDGJ
 
:grin:

Following is the simple Excel Formula to convert Julien to DD/MM/YYYY
It also take care of the leap year's

=DATE(1900+INT(<CellName>/1000),1,MOD(<CellName>,1000))

Try This
 
Converting A Standard Date To A Julian Date

The formula below will convert a standard Excel date in A1 to a Julian Date.

=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")

This formula takes the 2 right-most characters of the YEAR of the date in A1, and then appends the number of days between the date in A1 and the 0th day of that year. The TEXT function formats the day-of-year number as three digits, with leading zeros if necessary.

Converting A Julian Date To A Standard Date

The formula below will convert a Julian date to a standard Excel date.

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

If the year digits of the Julian date are less than 30 (i.e., 00 to 29), the date is assumed to be a 2000 century year. If the year digits of the Julian date are greater than or equal to 30 (i.e., 30 to 99), the date is assumed to be a 1900 century year. This formula works by taking advantage of the fact that the DATE function can handle days beyond the "normal" days in a month. For example, DATE correctly computes 100-Jan-1999 to be 10-April-1999.

These Julian dates must have the leading zero or zeros for years between 2000 and 2009. For example the 123rd day of 2000 must be entered as 00123. Format the cell as TEXT before entering the data, or enter an apostrophe before the Julian date -- e.g., '00123. This will prevent Excel from treating the Julian date as a number and suppressing the leading zeros.

Differences Between Two Julian Dates

Now that we know how to convert a Julian date to a standard date, it is simple to compute the number of days between two Julian dates. For example, suppose we have two Julian dates, in A1 and A2. The formula below will subtract the two dates.

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3)) -
DATE(IF(0+(LEFT(A2,2))<30,2000,1900)+LEFT(A2,2),1,RIGHT(A2,3))

Be sure to format the cell containing this formula as General.

Add A Number Of Days To A Julian Date

You can add some number of days to a Julian date. Suppose we have a Julian date in A1, and a number of days in A2. The formula below will add the number of days in A2 to the Julian date in A1, and return the date as a standard Excel date.

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3)+A2)

This formula works just like the formula we used to convert a Julian date to a standard date, but adds A2 to the days. This works because the DATE function will correctly compute a date like 400-Jan-1999 to be 4-Feb-2000. For example, if A1 contains 99001 (1-Jan-1999), and A2 contains 400, the formula will return 5-Feb-2000.

If you want your result to be a Julian date, then use the following formula, which is just our first formula to convert standard dates to Julian dates, with the formula above inserted into it wherever we need the date.

=RIGHT(YEAR(DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+
LEFT(A1,2),1,RIGHT(A1,3)+A2)),2)& TEXT(DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3)+A2)-
DATE(YEAR(DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+
LEFT(A1,2),1,RIGHT(A1,3)+A2)),1,0),"000")

For example, if A1 contains 99001 (1-Jan-1999), and A2 contains 400, the formula will return 00036, the 36th day of the year 2000.



VBA Procedures For Working With Julian Dates

The functions below will convert a Julian date to a standard Excel date, and convert a standard Excel date to a Julian date.

The JDateToDate function will accept a five-character Julian date and return the standard Excel date.

Function JDateToDate(JDate As String) As Long
Dim TheYear As Integer
Dim TheDay As Integer
Dim TheDate As Long

TheYear = CInt(Left(JDate, 2))
If TheYear < 30 Then
TheYear = TheYear + 2000
Else
TheYear = TheYear + 1900
End If

TheDay = CInt(Right(JDate, 3))
TheDate = DateSerial(TheYear, 1, TheDay)
JDateToDate = TheDate

End Function


The DateToJDate function will accept a standard Excel date and return a five-character string representing the Julian date.

Function DateToJDate(TheDate As Long) As String
Dim TheYear As Integer
Dim TheDays As Integer
Dim JDate As String

TheYear = Year(TheDate)
TheDays = DateDiff("d", DateSerial(TheYear, 1, 0), TheDate)
JDate = Right(Format(TheYear, "0000"), 2) & Format(TheDays, "000")
DateToJDate = JDate

End Function
 
Select cast('1/1/'+cast(1900+floor(gldgj/1000)as char)as
datetime)+cast(gldgj as int)%1000-1
from PRODDTA.F0911

One more version.

bala
 
Thanks, Bala. Not quite as pretty as Sally's but certainly prettier and more efficient than mine.
 
If you use the ODA ODBC driver that comes with Enterprise One it will convert the dates for you. It will also enter the implied decimals, so that data appears as it does in an application display rather than how it looks through UTB.
 
Re: Julian date conversion (DB2 SQL Syntax)

DB2 SQL Syntax

IFNULL( DATE( DAYS( DATE( SUBSTR( DIGITS( DECIMAL( ABUPMJ + 1900000, 7, 0 ) ), 1, 4 ) CONCAT '-01-01' ) ) + MOD( ABUPMJ, 1000 ) - 1 ), '1899-12-31' )

…where ABUPMJ is a field name from F0101 Address Book Master file.

see Julian Date Converter
 
Re: Julian date conversion - Sally\'s solution in MS-SQL?

MS SQL Server Syntax

dateadd(
dayofyear,
convert(integer,right(convert(varchar,abupmj),3))-1,
convert(datetime, convert(varchar,convert(integer, (1900000+abupmj)/1000)) + '-01-01'))
abupmg -- Date Updated (Gregorian)

…where ABUPMJ is a field name from F0101 Address Book Master file.

see Julian Date Converter
 
Re: Julian date conversion - Sally\'s solution in MS-SQL?

This simple SQL works for me on the AS/400:

Date( char( 1900000+w4APDT )) as Invoice_Date
 
Re: Julian date conversion - Sally\'s solution in MS-SQL?

[ QUOTE ]

This simple SQL works for me on the AS/400:

Date( char( 1900000+w4APDT )) as Invoice_Date

[/ QUOTE ]

I like your syntax for simplicity. I guess the main difference is that solution you gave does not handle "bad" dates like 105367 while more "complex" solution works:

select
DATE( DAYS( DATE( SUBSTR( DIGITS( DECIMAL( 105367 + 1900000, 7, 0 ) ), 1, 4 ) CONCAT '-01-01' ) ) + MOD( 105367, 1000 ) - 1 )
from
otgdb2.dual

…returns 01/02/2006

I would agree that it may not be appropriate to "assume" that 105367 is 2-Jan-2006, but this was done with the same concept in mind as in other programming languages. For example, you will see the same behavior in VB:

?dateserial(Year:=2005,Month:=12,Day:=33)
1/2/2006
 
Back
Top