Converting Julian Date To Gregorian Date in MS Access Query

Question

I have a query that includes a field that stores the ShipDate in julian date format.  Is there an expression or code I can write in the SQL select statement that will convert or display the values returned in this field as standard Gregorian date from Julian?

---

V/R, John B.

Answer 1

There are many varriations of a Julian date - what is yours?

Answer 2

I have a query that includes a field that stores the ShipDate in julian date format.  Is there an expression or code I can write in the SQL select statement that will convert or display the values returned in this field as standard Gregorian date from Julian?

First, let's be clear on exactly how the ShipDate is stored.  If it's actually a date/time field, just *displayed* in the Julian format (which I'm going to assume is YYYY.DDD or YY.DDD, or something like that), then you don't have to do anything but change the format when the field is displayed. 

However, if ShipDate is actually a text field, then you can convert it easily using the DateSerial function once you have split it into the two parts, year and day-of-year.  For example, if the format is YYYY.DDD or YY.DDD, then this would do it:

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

 

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 3

That is a new varaition I have not seen before with a period. 

Answer 4

That is a new varaition I have not seen before with a period. 

I used to see that a lot back in the old mainframe days.  Your question to the OP is apt: there's no standard "Julian" format, and for that matter a true Julian date is something else again, but I learned to call year+day notation "Julian" a long time ago, and I usually assume that's what people mean.  I could be totally wrong in this case.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

My military Julian is single digit for year and three for day of year -- 7/22/2011  =  1203

Answer 6

My military Julian is single digit for year and three for day of year -- 7/22/2011  =  1203

You military types must not look very far ahead -- or back.  :-)

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 7

Five years.

Answer 8

Hi JBLT83,

 

Thank you for posting in our forum.

 

Is there an expression or code I can write in the SQL select statement that will convert or display the values returned in this field as standard Gregorian date from Julian?

 

You can try:

 

select DateSerial(Left([JulianField],2),1,Right([JulianField],3))

from mytable

 

You can also write a function in your database. Just as what Dirk wrote for us.

Then in the query we can use the function directly.

 

select JulianToDate(mytable.[Myfield])

from mytable

 

You can read the following article as reference. The article is mainly about How to Convert Julian Days to Dates in Access and Back.

 

http://support.microsoft.com/kb/162745

 

I hope this will help you resolve the problem.

If the problem persists or I misunderstood anything, please feel free to let me know.

 

Best Regards,

---

Macy Dong [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.