rjordanbots
asked on
Convert 7 Digit Julian Date To A Calendar Date MMDDYYYY in a Access Query
I have a 7 digit number stored in a text field that is actually a Julian Date that needs to be converted to a calendar date, format mmddyyyy in a Microsoft Access Query.
Try using DateSerial(Left([FieldName ],2),1,Rig ht([FieldN ame],3))
are you sure that what you have are Julian dates?
post sample values.
you can use this code for conversion
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 = Format(DateSerial(YYYY, 1, JulDay), "m/d/yyyy")
End Function
' JulDay: The ordinal day of a year. Between 1 and 365 for all
' years, or between 1 and 366 for leap years.
'
' YYYY: A three or four digit integer for a year that is within the
' range of valid Microsoft Access dates. If YYYY is omitted,
' then YYYY is assumed to be the year of the current system
' date.
'
codes taken from this link
post sample values.
you can use this code for conversion
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 = Format(DateSerial(YYYY, 1, JulDay), "m/d/yyyy")
End Function
' JulDay: The ordinal day of a year. Between 1 and 365 for all
' years, or between 1 and 366 for leap years.
'
' YYYY: A three or four digit integer for a year that is within the
' range of valid Microsoft Access dates. If YYYY is omitted,
' then YYYY is assumed to be the year of the current system
' date.
'
codes taken from this link
Hi rjordanbots,
select DateSerial(Left([JulianFie ld],2),1,R ight([Juli anField],3 ))
from table
You can also write a function in your database. like this_
Then in the query we can use the function directly.
select JulianToDate(table.[Myfiel d])
from table
More Refrence here: http://support.microsoft.com/kb/162745
Thanks & Regards,
Krunal T. Tailor
select DateSerial(Left([JulianFie
from table
You can also write a function in your database. like this_
Function JulianToDate(JulianDate As String) As Date
Dim astrDatePart() As String
astrDatePart = Split(JulianDate, ".")
JulianToDate = DateSerial(CInt(astrDatePart(0)), 1, CInt(astrDatePart(1)))
End Function
Then in the query we can use the function directly.
select JulianToDate(table.[Myfiel
from table
More Refrence here: http://support.microsoft.com/kb/162745
Thanks & Regards,
Krunal T. Tailor
ASKER
Can you tell me what Julian Date 2032306 should convert to ?
I believe it should be 11012032
I believe it should be 11012032
Hi rjordanbots,
Please check over here http://aa.usno.navy.mil/data/docs/JulianDate.php
There are so many online converter available like_
1) http://www.onlineconversion.com/julian_date.htm
2) http://www.aavso.org/jd-calculator
Thanks & Regards,
Krunal T. Tailor
Please check over here http://aa.usno.navy.mil/data/docs/JulianDate.php
There are so many online converter available like_
1) http://www.onlineconversion.com/julian_date.htm
2) http://www.aavso.org/jd-calculator
Thanks & Regards,
Krunal T. Tailor
if that (2032306) is a julian date the conversion is 7/22/2306 using the function above
ASKER
This number is coming off of a Ibm I-Series MainFrame. I believe the first four digits is the year then the last three is the number of days. Can this be converted in Access to an actual date ?
If you have Julien dates...@Krunal has your solution.
That code is solid.
That code is solid.
Oh, data dump?
Yes. Use the right and left functions.
Yes. Use the right and left functions.
believe the first four digits is the year then the last three is the number of days. Can this be converted in Access to an actual date ? Yes
using the function
CJulian2Date(306,2032)
the value will be 11/1/2032
using the function
CJulian2Date(306,2032)
the value will be 11/1/2032
Yes, Rey Obrero is correct.
Please check here more ref: http://support.microsoft.com/kb/162745
Something like this:
CJulian2Date(32, 1996)
Note that your system's date format for 2/1/96 is returned. If your system's date is in 1996 then CJulian2Date(32) will also return 2/1/96.
Thanks & Regards,
Krunal T. Tailor
Please check here more ref: http://support.microsoft.com/kb/162745
Something like this:
CJulian2Date(32, 1996)
Note that your system's date format for 2/1/96 is returned. If your system's date is in 1996 then CJulian2Date(32) will also return 2/1/96.
Thanks & Regards,
Krunal T. Tailor
ASKER
The first four digits is the year 2032, the last 3 are the # of days, which is 306, need to convert the last 3 digits, which appears to be the julian day to an actual date
The date should read 11012032
The date should read 11012032
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help, all of this information was very helpful.