Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Julian Date (2000)

    I want to convert the julian date to date and hour. By now I'm just converting to the date...but I need the hour and minutes to.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Julian Date (2000)

    Can you specify what kind of Julian date you are using? In the thread starting at <post#=299024>post 299024</post#>, another Lounger asked about Julian dates and it turned out that the term "Julian date" is used for completely different things:
    - The number of days since noon, Universal Time on January 1, 4713 BCE, and
    - The day number within the current year.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date (2000)

    No, is starting in 01/01/1970 and is in second...for example today is 1066427598. What I do to convert is date: DateAdd("d",[dateTimeOrigination]/86400,#1/1/1970#) This formula just say today is 10/20/2003. But I need the hour,minutes and seconds.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Julian Date (2000)

    Sorry to keep on asking questions. Does this Julian date start on 1/1/1970 at midnight, or at noon (like the Julian date system I referred to)?

  5. #5
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date (2000)

    At midnight.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Julian Date (2000)

    Thanks. Try this expression:

    [dateTimeOrigination]/86400+DateSerial(1970,1,1)

  7. #7
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date (2000)

    Great! It Work! Thanks!

  8. #8
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date (2000)

    What is the formula if it start at noon?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Julian Date (2000)

    Access (and Windows) use one day as unit in date and time calculations. If the Julian date system started at 12:00 noon on January 1, 1970, you'd have to subtract 12 hours = 1/2 day from the result of the expression I posted earlier. So:

    [dateTimeOrigination]/86400+DateSerial(1970,1,1)-0.5

    or:

    [dateTimeOrigination]/86400+DateSerial(1970,1,1)-TimeSerial(12,0,0)

  10. #10
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date (2000)

    Hans, both formula return a decimal number

  11. #11
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date (2000)

    Hans, the correct formula working is
    [dateTimeOrigination]/86400+((DateSerial(1970,1,1))+TimeSerial(-4,0,0))

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Julian Date (2000)

    1. If you get a decimal number instead of a date, just set the Format property for the field or control to a date / time format.

    2. I hadn't taken time zones into account in my formula. If the Julian date starts on midnight UTC (aka Greenwich Mean Time), you have to adjust for your time zone to get the correct result - and you will have to adjust for daylight saving time too, if that applies to where you live.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •