Julian (I think) Calendar - Need Conversion To Standard dd/mm/yyyy

[Guest]

I need to figure out how to convert dates in this format into the standard
date format.

It looks like this: 1050930.

It needs to look like this: 09/30/2005 ( I think the above "105" is "2005")

Thoughts?

 

[Guest]

Is this data from an old COBOL system per chance? That extra character was a
way to add the century using only one character.

Below should work for DD/MM/YYYY formatted dates.

PW_Date:
CDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField], 4,2) &
"/" & mid([YourTextDateField], 2,2))

The problem is that CDate will bomb if the data presented can't be evaluated
as a date. Therefore I usually use an IIf statement and IsDate to check for a
valid date. If the date can't be evaluated properly, it returns a bogus date.

PW_Date:
IIf(IsDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField],
4,2) & "/" & mid([YourTextDateField], 2,2)), CDate(right([YourTextDateField],
2) & "/" & mid([YourTextDateField], 4,2) & "/" & mid([YourTextDateField],
2,2)), #1/1/1111#)

 

[Guest]

Your code is putting it into the right format, but the data is not right.

Example:

1050930 should equal 09/30/2005. Instead, it is coming out as 09/05/1930.

1060228 should equal 02/28/2006. Instead, it is coming out as 02/06/2028.

Thanks for your help! Much appreciated!

Is this data from an old COBOL system per chance? That extra character was a
way to add the century using only one character.

Below should work for DD/MM/YYYY formatted dates.

PW_Date:
CDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField], 4,2) &
"/" & mid([YourTextDateField], 2,2))

The problem is that CDate will bomb if the data presented can't be evaluated
as a date. Therefore I usually use an IIf statement and IsDate to check for a
valid date. If the date can't be evaluated properly, it returns a bogus date.

PW_Date:
IIf(IsDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField],
4,2) & "/" & mid([YourTextDateField], 2,2)), CDate(right([YourTextDateField],
2) & "/" & mid([YourTextDateField], 4,2) & "/" & mid([YourTextDateField],
2,2)), #1/1/1111#)
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I need to figure out how to convert dates in this format into the standard
date format.

It looks like this: 1050930.

It needs to look like this: 09/30/2005 ( I think the above "105" is "2005")

Thoughts?

 

[Guest]

By the way, I receive the same results with both of the provided choices.

Your code is putting it into the right format, but the data is not right.

Example:

1050930 should equal 09/30/2005. Instead, it is coming out as 09/05/1930.

1060228 should equal 02/28/2006. Instead, it is coming out as 02/06/2028.

Thanks for your help! Much appreciated!

Is this data from an old COBOL system per chance? That extra character was a
way to add the century using only one character.

Below should work for DD/MM/YYYY formatted dates.

PW_Date:
CDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField], 4,2) &
"/" & mid([YourTextDateField], 2,2))

The problem is that CDate will bomb if the data presented can't be evaluated
as a date. Therefore I usually use an IIf statement and IsDate to check for a
valid date. If the date can't be evaluated properly, it returns a bogus date.

PW_Date:
IIf(IsDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField],
4,2) & "/" & mid([YourTextDateField], 2,2)), CDate(right([YourTextDateField],
2) & "/" & mid([YourTextDateField], 4,2) & "/" & mid([YourTextDateField],
2,2)), #1/1/1111#)
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I need to figure out how to convert dates in this format into the standard
date format.

It looks like this: 1050930.

It needs to look like this: 09/30/2005 ( I think the above "105" is "2005")

Thoughts?

 

[Albert D.Kallal]

I would build a function that you can use anywhere on the existing data.

The function is FAR easer to read then a complex expression. And, likely you
would not notice the difference in speed with 100,000 records.

So, here is a function that should do the trick...


Public Function JDate(dt As Variant) As Variant

If IsNull(dt) Then Exit Function

Dim d As Integer ' day
Dim m As Integer ' month
Dim y As Integer ' year

y = 1999 + Left(dt, 1) + Mid(dt, 2, 2)
m = Mid(dt, 4, 2)
d = Right(dt, 2)

JDate = DateSerial(y, m, d)

End Function

In a query, you can use

myNDATE: Jdate([OldDateFormatField])

In a form, you can use

=(Jdate([oldField]))

 

[Douglas J. Steele]

Um, your subject line says dd/mm/yyyy, but your example is mm/dd/yyyy. Which
do you want?

Regardless, try:

DateSerial(Mid([YourTextDateField], 2,2), Mid([YourTextDateField], 4,2),
Right([[YourTextDateField], 2))

 

[Douglas J. Steele]

Hate to quibble, Jerry, but it's seldom a good idea to use CDate in that
manner unless you can be guaranteed that all of the users will have their
Short Date format set to dd/mm/yyyy in Regional Settings. CDate respects the
user's Date settings, so if they have, for instance, mm/dd/yyyy, your going
to run into problems, especially using 2 digit years. Far safer to use
DateSerial so that it doesn't matter.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is this data from an old COBOL system per chance? That extra character was
a
way to add the century using only one character.

Below should work for DD/MM/YYYY formatted dates.

PW_Date:
CDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField], 4,2)
&
"/" & mid([YourTextDateField], 2,2))

The problem is that CDate will bomb if the data presented can't be
evaluated
as a date. Therefore I usually use an IIf statement and IsDate to check
for a
valid date. If the date can't be evaluated properly, it returns a bogus
date.

PW_Date:
IIf(IsDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField],
4,2) & "/" & mid([YourTextDateField], 2,2)),
CDate(right([YourTextDateField],
2) & "/" & mid([YourTextDateField], 4,2) & "/" & mid([YourTextDateField],
2,2)), #1/1/1111#)
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I need to figure out how to convert dates in this format into the
standard
date format.

It looks like this: 1050930.

It needs to look like this: 09/30/2005 ( I think the above "105" is
"2005")

Thoughts?

 

[Guest]

I'll add that to my gray matter and use your DateSerial example in the
future. Do you think it wise to still check for IsDate?

Thanks,
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hate to quibble, Jerry, but it's seldom a good idea to use CDate in that
manner unless you can be guaranteed that all of the users will have their
Short Date format set to dd/mm/yyyy in Regional Settings. CDate respects the
user's Date settings, so if they have, for instance, mm/dd/yyyy, your going
to run into problems, especially using 2 digit years. Far safer to use
DateSerial so that it doesn't matter.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is this data from an old COBOL system per chance? That extra character was
a
way to add the century using only one character.

Below should work for DD/MM/YYYY formatted dates.

PW_Date:
CDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField], 4,2)
&
"/" & mid([YourTextDateField], 2,2))

The problem is that CDate will bomb if the data presented can't be
evaluated
as a date. Therefore I usually use an IIf statement and IsDate to check
for a
valid date. If the date can't be evaluated properly, it returns a bogus
date.

PW_Date:
IIf(IsDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField],
4,2) & "/" & mid([YourTextDateField], 2,2)),
CDate(right([YourTextDateField],
2) & "/" & mid([YourTextDateField], 4,2) & "/" & mid([YourTextDateField],
2,2)), #1/1/1111#)
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I need to figure out how to convert dates in this format into the
standard
date format.

It looks like this: 1050930.

It needs to look like this: 09/30/2005 ( I think the above "105" is
"2005")

Thoughts?

 

[James A. Fortune]

I need to figure out how to convert dates in this format into the standard
date format.

It looks like this: 1050930.

It needs to look like this: 09/30/2005 ( I think the above "105" is "2005")

Thoughts?

I think that the name "Julian" for this format is a very misleading
choice. The Julian Calendar has a very specific meaning that gets lost
when the name "Julian Format" is used, especially when used in
conjunction with the word "conversion." The Julian calendar has one
leap year every four years.

James A. Fortune
(e-mail address removed)

The astrologers of Ptolemaic Egypt, whose discipline spread all over the
Greek and Roman world, held that each hour was subject to a planet in
descending order of distance from the Earth, namely Saturn, Jupiter,
Mars, Sun, Venus, Mercury, Moon, and each day to the planet controlling
its first daytime hour; since the day consists of 3 X 7 + 3 hours, each
day was governed by the third planet from that which governed the day
before, giving the cycle Saturn, Sun, Moon, Mars, Mercury, Jupiter, Venus.


Speakers of other languages proved even more resistant, above all in
Britain, where both Welsh and English have preserved the pagan names
intact, Welsh directly (dydd Sul, dydd Llun, dydd Mawrth, dydd Mercher,
dydd Iau, dydd Gwener, dydd Sadwrn; similar forms in Cornish and
Breton), English with the Germanic deities Tiw, Woden (the god of
eloquence and wisdom), Thunor, and Frig substituted respectively for
Mars, Mercury, Jupiter, and Venus, but Saturn, for whom no counterpart
was recognized, left unchanged.

-- The Oxford Companion to the Year, Bonnie Blackburn & Leofranc
Holford-Strevens 1999 pp. 566-567.

 

[Douglas J Steele]

Not really (but I'm not as adamant on this one.)

IsDate is going to try its best to please you and find some way of looking
at the string so that it is a date. Especially when you've only got 2 digit
years, what it accepts as a valid date may not be the date you want.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'll add that to my gray matter and use your DateSerial example in the
future. Do you think it wise to still check for IsDate?

Thanks,
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hate to quibble, Jerry, but it's seldom a good idea to use CDate in that
manner unless you can be guaranteed that all of the users will have their
Short Date format set to dd/mm/yyyy in Regional Settings. CDate respects the
user's Date settings, so if they have, for instance, mm/dd/yyyy, your going
to run into problems, especially using 2 digit years. Far safer to use
DateSerial so that it doesn't matter.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is this data from an old COBOL system per chance? That extra character was
a
way to add the century using only one character.

Below should work for DD/MM/YYYY formatted dates.

PW_Date:
CDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField], 4,2)
&
"/" & mid([YourTextDateField], 2,2))

The problem is that CDate will bomb if the data presented can't be
evaluated
as a date. Therefore I usually use an IIf statement and IsDate to check
for a
valid date. If the date can't be evaluated properly, it returns a bogus
date.

PW_Date:
IIf(IsDate(right([YourTextDateField], 2) & "/" & mid([YourTextDateField],
4,2) & "/" & mid([YourTextDateField], 2,2)),
CDate(right([YourTextDateField],
2) & "/" & mid([YourTextDateField], 4,2) & "/" & mid([YourTextDateField],
2,2)), #1/1/1111#)
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I need to figure out how to convert dates in this format into the
standard
date format.

It looks like this: 1050930.

It needs to look like this: 09/30/2005 ( I think the above "105" is
"2005")

Thoughts?

 

[Ron2005]

Besides Julian date is typically in the format YYDDD

YY - 2 digit year
DDD - day of the year (1-366)

And I do not know how anysystem using that date handled Y2K although I
worked on one system where they simply converted it to YYYYDDD.

More info for someone's trivia bag.

 

[Guest]

Here is what I did, and it seems to work. Let me know what y'all think.

Remember, the date format is: 1050930.
I need it to read like this: 09/30/2005

I figured out a way to do this in one query.

Expr1: Right([DateField],2)
Expr2: Mid([DateField],4,2)
Expr3: 1999+Left([DateField],1)+Mid([DateField],2,2)
Expr4: [Expr2] & "/" & [Expr1] & "/" & [Expr3]

This gives the following result: 09/30/2005

Which is exactly what I need...

 

[Douglas J Steele]

Besides Julian date is typically in the format YYDDD

YY - 2 digit year
DDD - day of the year (1-366)

That's one definition of Julian date, and while it's a common one, it's
actually not the correct one.

According to http://aa.usno.navy.mil/data/docs/JulianDate.html "Julian dates
(abbreviated JD) are simply a continuous count of days and fractions since
noon Universal Time on January 1, 4713 BCE (on the Julian calendar). Almost
2.5 million days have transpired since this date. Julian dates are widely
used as time variables within astronomical software. Typically, a 64-bit
floating point (double precision) variable can represent an epoch expressed
as a Julian date to about 1 millisecond precision. Note that the time scale
that is the basis for Julian dates is Universal Time, and that 0h UT
corresponds to a Julian date fraction of 0.5"

16:00 UT today (January 24, 2006) equates to 2453760.16667

More info for someone's trivia bag.

And even more!