none
Julian Date to Calendar date Conversion RRS feed

  • Question

  • Hello,

    I have a date that is Julian date and I would like to convert it to the calendar date.

    Is there a function  or a  Expression  for MS Access query that I can use?

    The Julian date format that I'm using is: 2458003 and the corresponding Calendar date should be :09/06/2017


    Wednesday, September 6, 2017 3:38 PM

All replies

  • Hi,

    If I convert today's date (9/6/17) into Julian date, it would turn out to become 17249 or maybe 2017249.

    Can you give us a logic for converting today's date (9/6/17) into a Julian date with a value of 2458003?

    Thanks.

    Wednesday, September 6, 2017 4:11 PM
  • Using the standard algorithm for returning a Julian day as a Gregorian date:

    Public Function JulianToGregorian(jd As Long) As Date

        Dim i As Long
        Dim j As Long
        Dim l As Long
        Dim n As Long

        Dim y As Integer
        Dim m As Integer
        Dim d As Integer
        
        l = jd + 68569
        n = (4 * l) \ 146097
        l = l - (146097 * n + 3) \ 4
        i = (4000 * (l + 1)) \ 1461001
        l = l - (1461 * i) \ 4 + 31
        j = (80 * l) \ 2447
        d = l - (2447 * j) \ 80
        l = j \ 11
        m = j + 2 - (12 * l)
        y = 100 * (n - 49) + i + l
        
        JulianToGregorian = DateSerial(y, m, d)
        
    End Function

    You can see how it works in the debug window, returning the date in the ISO standard format for date notation of YYYY-MM-DD:

    ? Format(JulianToGregorian(2458003),"yyyy-mm-dd")
    2017-09-06

    For definitions see:

    http://mooring.ucsd.edu/software/matlab/mfiles/toolbox/misc/timefun/doc/pm_julian.txt


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, September 6, 2017 5:45 PM hyperlink corrected
    • Proposed as answer by Tony---- Tuesday, September 12, 2017 2:07 AM
    Wednesday, September 6, 2017 5:44 PM
  • In the military we used a 4 digit Julian date - one character for year and three digits for the day of the year.

    There are many other forms of "Julian Date" in use - see below from Wikipedia --

    Name Epoch Calculation <abbr title="15:56, 6 September 2017 (UTC)">Current value</abbr> Notes
    Julian Date 12h Jan 1, 4713 BC 2458003.16389
    Reduced JD 12h Nov 16, 1858 JD − 2400000 58003.16389 <sup class="reference" id="cite_ref-10">[10]</sup><sup class="reference" id="cite_ref-11">[11]</sup>
    Modified JD 0h Nov 17, 1858 JD − 2400000.5 58002.66389 Introduced by SAO in 1957
    Truncated JD 0h May 24, 1968 floor (JD − 2440000.5) 18002 Introduced by NASA in 1979
    Dublin JD 12h Dec 31, 1899 JD − 2415020 42983.16389 Introduced by the IAU in 1955
    CNES JD 0h Jan 1, 1950 JD − 2433282.5 24720.66389 Introduced by the CNES<sup class="reference" id="cite_ref-_TPtime_12-0">[12]</sup>
    CCSDS JD 0h Jan 1, 1958 JD − 2436204.5 21798.66389 Introduced by the CCSDS<sup class="reference" id="cite_ref-_TPtime_12-1">[12]</sup>
    LOP JD 0h Jan 1, 1992 JD − 2448622.5 9380.66389 Introduced by the LOP<sup class="reference" id="cite_ref-_TPtime_12-2">[12]</sup>
    Lilian date Oct 15, 1582<sup class="reference" id="cite_ref-_1_13-0">[13]</sup> floor (JD − 2299159.5) 158843 Count of days of the Gregorian calendar
    Rata Die Jan 1, 1<sup class="reference" id="cite_ref-_1_13-1">[13]</sup> proleptic Gregorian calendar floor (JD − 1721424.5) 736578 Count of days of the Common Era
    Unix Time 0h Jan 1, 1970 (JD − 2440587.5) × 86400 1504713418 Count of seconds<sup class="reference" id="cite_ref-14">[14]</sup>
    Mars Sol Date 12h Dec 29, 1873 (JD − 2405522)/1.02749 51076.99349 Count of Martian days


    Build a little, test a little

    Wednesday, September 6, 2017 5:45 PM
  • Hi Ken,

    Thanks for the clarification. I was only aware of the military version as QA said.

    Cheers!

    Wednesday, September 6, 2017 6:10 PM
  • Hello Jorgemme,

    Does the code shared by Ken work for you?

    If your issue has been resolved, I suggest you mark helpful posts or your solution as answer to close this thread.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 15, 2017 4:21 AM
    Moderator