how do I convert a julian date(JDEdwards) into a calendar date

[Tom Fontaine]

working with JD Edwards database, all date are stored in Julian
Example:
107001 = jan 1 2007

There must be a function or some VBA code that does the conversion to
01/01/07 or something similar

 

[Jerry Whittle]

I could of sworn that I saw something recently just on this subject but a
search didn't turn up anything.

This works but is ugly. There has to be a better way.

DateAdd("d",Int(Right("107001",3)-1), CDate("1/1/" & Mid("107001",2,2)))

 

[John Spencer]

The following may work for you.

Assumptions:
-- Dates with Calculated years greater than 30 are in 19xx and dates from 0
to 29 are in 20xx
-- 107001 represents the 21st century, 7th year, 1st day of year.
-- 045028 represents the 20th century, 45th year, 28th day of the year

If you have dates that are before 1930 then you would need something more
complex to determine the year. And if you have dates after 2029 then the
same applies.

DateSerial(([JDDate] \1000) Mod 100,1, [JDDate] Mod 1000)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

If you have dates that are before 1930 then you would need something more
complex to determine the year. And if you have dates after 2029 then the
same applies.

DateSerial(([JDDate] \1000) Mod 100,1, [JDDate] Mod 1000)

How about passing the four digit year?

DateSerial(1900 + [JDDate]\1000, 1, [JDDate] MOD 1000)


John W. Vinson [MVP]

 

[John Spencer]

Nice modification!!

and that should let the user get dates for years from 1900 to 2899.

Again assuming that the first digit represents the century (0 = 1900, ...9 =
2800)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

If you have dates that are before 1930 then you would need something more
complex to determine the year. And if you have dates after 2029 then the
same applies.

DateSerial(([JDDate] \1000) Mod 100,1, [JDDate] Mod 1000)

How about passing the four digit year?

DateSerial(1900 + [JDDate]\1000, 1, [JDDate] MOD 1000)


John W. Vinson [MVP]