Strange Julian Date Phenomenon

[tkosel]

I discovered something weird in one of my applications. To make a long story
short, an incorrect Julian date was being calculated. After much
scrutinization, it appeared that it was doing this becuase of the way I was
formatting the date. (dd/mm/yyyy versus mm/dd/yyyy) (The second to the last
example is where I discovered my mistake.)
I conducted the following experiment.


Use of Reversed (European Format) Month and Day Code (Correct Julian Date)
====================================================
Me.JulianExpirationDate = Format(DateAdd("yyyy", 5, Now()), "dd/mm/yyyy")
? me.JulianExpirationDate
30/04/2015
Me.JulianExpirationDate = Format([JulianExpirationDate], "yy") &
Format(Format_([JulianExpirationDate], "y"), "000")
? me.JulianExpirationDate
15120

Use of Non-Reversed Month and Day Code (Also Correct Julian Date)
==============================================
Me.JulianExpirationDate = DateAdd("yyyy", 5, Now())
? me.JulianExpirationDate
4/30/2015 12:38:15 PM
Me.JulianExpirationDate = Format(Me.JulianExpirationDate, "yy") &
Format(Format_(Me.JulianExpirationDate, "y"), "000")
? me.JulianExpirationDate
15120


Same as Above (European Format) with a Specific Date (Incorrect Julian Date)
===================================================
Me.JulianExpirationDate = Format(DateAdd("yyyy", 5, #02/08/2010#),
"dd/mm/yyyy")
? me.JulianExpirationDate
08/02/2015
Me.JulianExpirationDate = Format([JulianExpirationDate], "yy") &
Format(Format_([JulianExpirationDate], "y"), "000")
? me.JulianExpirationDate
15214

Same as Above (Non-Reversed Month and Day Code) with a Specific Date
(Correct Julian Date)
=====================================================
Me.JulianExpirationDate = DateAdd("yyyy", 5, #02/08/2010#)
? me.JulianExpirationDate
2/8/2015
Me.JulianExpirationDate = Format([JulianExpirationDate], "yy") &
Format(Format_([JulianExpirationDate], "y"), "000")
? me.JulianExpirationDate
15039


Can anyone tell me why the difference in the last two examples and why the
first two yeild the same results?

 

[John W. Vinson]

I discovered something weird in one of my applications. To make a long story
short, an incorrect Julian date was being calculated. After much
scrutinization, it appeared that it was doing this becuase of the way I was
formatting the date. (dd/mm/yyyy versus mm/dd/yyyy) (The second to the last
example is where I discovered my mistake.)

What you're doing is converting a Date/Time value (date) to a text string...
and then treating that text string as a date. The Format() function recognizes
date/time values according to the Microsoft conventions - and since the
Microsoft programmers were American (or at least had American bosses), they
used the American mm/dd/yyyy syntax as the default. The Format() function (and
queries, and other code) ignores the Windows regional setting.

Just calculate the Julian date directly from Date() or from a date/time field
stored in your table, without these nested Format() function calls.

 

[tkosel]

Mr. Vinson,

Thanks for your response, and it makes sense to me!!!! I know I made a
mistake when I used that "dd/mm/yyyy" format and have corrected it.

However, I need to explain to my customer why it worked some times (for some
dates) in the incorrect code and not at others. In my examples, you can see
that it worked fine for some dates, but not for others. Can you help me with
some logical explanation for that phenomena?

 

[John W. Vinson]

However, I need to explain to my customer why it worked some times (for some
dates) in the incorrect code and not at others. In my examples, you can see
that it worked fine for some dates, but not for others. Can you help me with
some logical explanation for that phenomena?

Access will struggle to interpret a date. The text string "3/10/2010" will be
interpreted as March 10 (even if the user intended it to be 3 October), but it
will give a JDate value. "4/30/2010" will similarly be April 30 (and it won't
try it the other way since there's no 30th month). "30/4/2010" will be
intepreted as April 30, even though it's d/m/y, since the alternative isn't a
valid date.

So it will read the date as mm/dd/yyyy if it makes sense that way; only if the
day number is greater than 12 will it try to turn it around.

 

[John Spencer]

Access can figure out that 30/8/2010 must be 8/30/2010. Since 30 is
obviously NOT a month. However with 10/8/2010 it cannot see a problem.
After all the 10 is a valid month number, so this is a valid date in
the format of mm/dd/yyyy.

I've run into this so often that I almost automatically process all date
strings in the unambiguous form of yyyy-mm-dd. I will still format and
display data in the appropriate format.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[tkosel]

Thanks to both Johns!! It makes perfect sense to me! To bad I messed up used
the formatiing I did. Do you think I am right in stating that the Julian
Date will only be incorrect for the first 12 days of each month? (Since
there is no 13th or larger month, Access will correct the date
automatically?) I did a test using May 12, 2015 and May 13, 2015. JD for
May 12, 2015 was wrong, JD for May 13, 2015 was correct!

Anyhow, thanks for your input here, it was great to get!!!!

 

[John W. Vinson]

Thanks to both Johns!! It makes perfect sense to me! To bad I messed up used
the formatiing I did. Do you think I am right in stating that the Julian
Date will only be incorrect for the first 12 days of each month?

(Since
there is no 13th or larger month, Access will correct the date
automatically?) I did a test using May 12, 2015 and May 13, 2015. JD for
May 12, 2015 was wrong, JD for May 13, 2015 was correct!

Exactly. Either 5/12 or 12/5 will be wrong but 13/5 will be switched around to
May 13.

 

[raskew via AccessMonster.com]

Hi -

I'm tryiing to figure out what your interpretation of 'Julian Date' is.
Suspect it's vastly incorrect. Do a Google on 'Julian Date'. Think you'll
be amazed.

Best wishes - Bob

 

[John W. Vinson]

I'm tryiing to figure out what your interpretation of 'Julian Date' is.
Suspect it's vastly incorrect. Do a Google on 'Julian Date'. Think you'll
be amazed.

It's evidently one of the many, many ways that term has been used. I can think
of several off the top of my head, all very different (Astronomical Julian
Date, Modified Astronomical, 2010122, 10122, the really cryptic "Y2K every ten
years" 0122, and so on.

 

[david]

A phenomina michael \(michka\) kaplan called "evil date guessing".

If you WANT to convert a string to a date, CDATE is better than
## because CDATE uses the default system settings, rather than
evil date guessing. For example:

?#1/2/2000#
2/01/2000
?cdate("1/2/2000")
1/02/2000

(david)

 

[Alfred Trietsch]

Maybe you can use the following function that interprets the date string
according to an explicitly given format and should work in many -if not all -
locales:

Public Function DecodeDate(strVal As String, strFmt As String) As Date
Dim Result As Date, Given As String, check As String
Dim Year As Integer, Month As Integer, Day As Integer
Result = CDate(strVal)
Given = Replace(strVal, "/", "-")
Given = Replace(Given, ".", "-")
check = Format(Result, strFmt)
check = Replace(check, "/", "-")
check = Replace(check, ".", "-")
If Given <> check Then
Year = DatePart("yyyy", Result)
Month = DatePart("d", Result) ' swap month and day
Day = DatePart("m", Result) ' swap month and day
Result = DateSerial(Year, Month, Day)
End If
DecodeDate = Result
End Function