Coverting Julian dates to standard dates in MS Access

[Jovix]

I'm putting together a report which is extracting data from our JD Edwards database. The dates are in Julian format. I need to know if there is a way to convert the julian date to conventional date within a query. for example...

If Julian date is 104092
Convert it to 04/01/04

Thanks in advance.
Jovix

 

[jmd0252]

My adive is to do the conversion, before you do the extraction. If you find a way for excel to make the change I would love to know it. The few times I have tried, it does not work out at all. Are you using using World or OneWorld?

 

[Jovix]

I found this for Excel, but I still want to do the conversion in Access. I don't want to export to excel, convert the dates, then import to access. It's too much work...the user who views the report will not want to do this.

=(YEAR(Date )-1900)*1000 +( Date - DATE(YEAR( Date ),1,1))

Replace Date with the date you want to convert.

 

[jmd0252]

Great Idea,, there is another web site for peoplesoft / jde software.. it is

http://www.jdelist.com/.

This would be a great question to post there, as lots of world, oneworld, and consultants frequent this site.

 

[Jovix]

I got the conversion to work, based on this link.

http://support.microsoft.com/default.aspx?scid=kb;...

 

[Jovix]

oops..here's the link again

http://support.microsoft.com/default.aspx?scid=kb;en-us;162745

 

[Jovix]

To resolve my problem I created a new Module and place the following code in it.

Function CJulian2Date (JulDay As Integer, Optional YYYY)
If IsMissing(YYYY) Then YYYY = Year(Date)
If Not IsNumeric(YYYY) Or YYYY \ 1 <> YYYY Or YYYY < 100 Or YYYY _
> 9999 Then Exit Function
If JulDay > 0 And JulDay < 366 Or JulDay = 366 And _
YYYY Mod 4 = 0 And YYYY Mod 100 <> 0 Or YYYY Mod 400 = 0 Then _
CJulian2Date = DateSerial(YYYY, 1, JulDay)
End Function

Then I created a new column in my query and I placed the following line for the field name.

Date: CJulian2Date([MyDate Mod 1000, 1900 + [MyDate] \ 1000)

MyDate should be replaced with the field name of the column that contains the Julian dates.

then I created my report based on this query and used the Date Field to show standard calender dates instead of julian dates.

Thats it...hope this helps someone else.

 

[jmd0252]

That is pretty kewl.. Very nice to have in your back pocket. Thanks

 

[kjv1611]

If any of you are reading this now, and perhaps have a little more experience and/or understanding of Julian dates, then maybe you could help me figure this out: Why would 13493 be an invalid Julian date, and how can I fix it? If you want more detail, please see thread705-971029 . I have also tried the microsoft function listed above before finding this thread, and was wondering why I was getting all Null values in the date field in the table where I was running the function. I looked back at the details listed with the function on the Microsoft page, and found this:

' RETURNS: A date for a valid Microsoft Access year and Julian day,
' or a Null value for an invalid Julian Day.

So, I'm guessing my date is invalid. Any assistance/ideas would be greatly appreciated.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[jmd0252]

Well Julian date in the Jd Edwards world work this way. First 3 digets are the year and century. Example 099, would be 1999, or 103 would be 2003. The last 3 digets are the day of the year. so the above date of 13493,, would be 1913, and the 493 day of the year, which is impossible of course, since the year has either 365, or 366.

 

[kjv1611]

Hmm.. so, maybe it's not actually a Julian date after all? Or are there multiple different standards for Julian dates? What's weird is if I have the values such as that in a field in Access, and simply change the format from Number to Date, it gives the correct date, but just not the correct year. I'd not be surprised if it's something altogether different from any standards here where I work.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Hmm.. so, maybe it's not actually a Julian date after all? Or are there multiple different standards for Julian dates? What's weird is if I have the values such as that in a field in Access, and simply change the format from Number to Date, it gives the correct date, but just not the correct year. I'd not be surprised if it's something altogether different from any standards here where I work.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[jmd0252]

Question,, you working on World on OneWorld. Because the standards could be different. If it was some sort of elapse counter,, that would be around 12 or 13 years. Ring on bells??

 

[kjv1611]

Sorry - it's from neither, I just assumed it was the same basic standard. The system is actually the Columbia Ultimate (CUBS) system. It runs in Pick Basic.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV