locked
Converting Julian Date To Gregorian Date in MS Access Query RRS feed

  • 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.
    Friday, July 22, 2011 2:22 PM

Answers

  • 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
    • Proposed as answer by Macy Dong Monday, August 1, 2011 9:12 AM
    • Marked as answer by Macy Dong Tuesday, August 2, 2011 9:31 AM
    Friday, July 22, 2011 3:05 PM
  • 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.

    • Proposed as answer by Macy Dong Monday, August 1, 2011 9:12 AM
    • Marked as answer by Macy Dong Tuesday, August 2, 2011 9:31 AM
    Thursday, July 28, 2011 6:39 AM

All replies

  • There are many varriations of a Julian date - what is yours?
    Friday, July 22, 2011 2:39 PM
  • 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
    • Proposed as answer by Macy Dong Monday, August 1, 2011 9:12 AM
    • Marked as answer by Macy Dong Tuesday, August 2, 2011 9:31 AM
    Friday, July 22, 2011 3:05 PM
  • That is a new varaition I have not seen before with a period. 
    Friday, July 22, 2011 3:13 PM
  • 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
    Friday, July 22, 2011 3:28 PM
  • My military Julian is single digit for year and three for day of year -- 7/22/2011  =  1203
    Friday, July 22, 2011 3:36 PM
  • 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
    • Edited by Dirk Goldgar Friday, July 22, 2011 7:01 PM fix typo
    Friday, July 22, 2011 3:44 PM
  • Five years.
    Friday, July 22, 2011 3:54 PM
  • 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.

    • Proposed as answer by Macy Dong Monday, August 1, 2011 9:12 AM
    • Marked as answer by Macy Dong Tuesday, August 2, 2011 9:31 AM
    Thursday, July 28, 2011 6:39 AM