convert Julian date to mm/dd/yyyy

[Guest]

I have a database with many tables holding julian dates. These tables were
created and loaded via passthrough queries to a JDEdwards database on an
AS/400. I need to convert these julian dates in my Access 2003 db to
mm/dd/yyyy format in several queries. Any help would be greatly appreciated!

Thanks,
Ray

 

[kingston via AccessMonster.com]

Try this:

NewDate = #12/31/2005# + [JulianDate]

Change 2005 to whatever the year before the input date is and take only the
days portion of the date for [JulianDate].

 

[Guest]

Changing code every year in an application should be avoided. Here is a
function that will convert Julian to Gregorian dates. If the formula you
posted works, then today is April 14, 2023

?#12/31/2005# + 06313
4/14/2023


Function JulDateToGregDate(ByVal varJulDate As Variant) As Date
Dim lngYearPart As Long
Dim lngDayPart As Long

varJulDate = Format(varJulDate, "00000")
lngYearPart = CLng(Left(varJulDate, 2))
If lngYearPart < 30 Then
lngYearPart = lngYearPart + 2000
Else
lngYearPart = lngYearPart + 1900
End If

lngDayPart = CLng(Right(varJulDate, 3))
JulDateToGregDate = DateSerial(lngYearPart, 1, lngDayPart)

End Function

Try this:

NewDate = #12/31/2005# + [JulianDate]

Change 2005 to whatever the year before the input date is and take only the
days portion of the date for [JulianDate].

I have a database with many tables holding julian dates. These tables were
created and loaded via passthrough queries to a JDEdwards database on an
AS/400. I need to convert these julian dates in my Access 2003 db to
mm/dd/yyyy format in several queries. Any help would be greatly appreciated!

Thanks,
Ray

 

[kingston via AccessMonster.com]

Yes, changing code every year should be avoided. I assumed that Eastbay66
could figure out how to extract the year and the day from the field. My
suggestion was that he extract the days portion and simply add it to the last
day of the previous year, not that he blindly add the input to 12/31/2005.
This is because I do not know whether his input is 2006123, 06123 or the
actual number of days since January 1, 4713 BC.

Changing code every year in an application should be avoided. Here is a
function that will convert Julian to Gregorian dates. If the formula you
posted works, then today is April 14, 2023

?#12/31/2005# + 06313
4/14/2023

Function JulDateToGregDate(ByVal varJulDate As Variant) As Date
Dim lngYearPart As Long
Dim lngDayPart As Long

varJulDate = Format(varJulDate, "00000")
lngYearPart = CLng(Left(varJulDate, 2))
If lngYearPart < 30 Then
lngYearPart = lngYearPart + 2000
Else
lngYearPart = lngYearPart + 1900
End If

lngDayPart = CLng(Right(varJulDate, 3))
JulDateToGregDate = DateSerial(lngYearPart, 1, lngDayPart)

End Function

Try this:

[quoted text clipped - 10 lines]

 

[Guest]

Yes, I missed the part about using just the days, but there is a problem with
number of days since January 1, 4713 BC.

January wasn't invented until 713 BC when it and February were added to the
old Roman calendar, but it was not the first month of the year until 153 BC.

The gregorian calendar was not introduced until 1582. The last day of the
Julian calendar was October 4 , 1582 and this was followed by the first day
of the Gregorian calendar October 15 , 1582. Not an error, 10 days
disappeared, but it was necessary to realign the caldenar so Easter would be
where the Pope thought it should be.

January wasn't invented until 713 BC when it and February were added to the
old Roman calendar, but it was not the first month of the year until 153 BC.

Now, on Tralfamador (my home planet) we have 12 30 day months followed by 5
days of Roman style partying (6 on leap year), but you would not be able to
pronounce January in my language

Yes, changing code every year should be avoided. I assumed that Eastbay66
could figure out how to extract the year and the day from the field. My
suggestion was that he extract the days portion and simply add it to the last
day of the previous year, not that he blindly add the input to 12/31/2005.
This is because I do not know whether his input is 2006123, 06123 or the
actual number of days since January 1, 4713 BC.

Changing code every year in an application should be avoided. Here is a
function that will convert Julian to Gregorian dates. If the formula you
posted works, then today is April 14, 2023

?#12/31/2005# + 06313
4/14/2023

Function JulDateToGregDate(ByVal varJulDate As Variant) As Date
Dim lngYearPart As Long
Dim lngDayPart As Long

varJulDate = Format(varJulDate, "00000")
lngYearPart = CLng(Left(varJulDate, 2))
If lngYearPart < 30 Then
lngYearPart = lngYearPart + 2000
Else
lngYearPart = lngYearPart + 1900
End If

lngDayPart = CLng(Right(varJulDate, 3))
JulDateToGregDate = DateSerial(lngYearPart, 1, lngDayPart)

End Function

Try this:

[quoted text clipped - 10 lines]

Thanks,
Ray