displaying dates from a linked oracle table

D

don

How do you display a date that has a two digit year, i.e 840902, so that it
comes out as 1984 and not 2084 (from an oracle linked table). In oracle you
can specify a mask of 'rr/mm/dd''. Is there a similiary date mask/format in
access??
 
J

John Vinson

How do you display a date that has a two digit year, i.e 840902, so that it
comes out as 1984 and not 2084 (from an oracle linked table). In oracle you
can specify a mask of 'rr/mm/dd''. Is there a similiary date mask/format in
access??
Click to expand...

Access by default treats two-digit years in the following manner: 00
through 29 are in the current (21st) century; 30 through 99 are in the
20th century. Entering 8/13/84 will in fact be interpreted as 1984.

An Access Date/Time value is actually stored as a double float number:
a count of days since midnight, December 30, 1899. They can be
formatted however you like - see the Custom formats under Date in the
online help. You can use yymmdd (840902), mm/dd/yy (09/02/84),
mm/dd/yyyy (09/02/1984), or a wide variety of other formats. These
don't affect what's stored in the database, just how it's displayed.

Just how Access is interpreting your linked Oracle table date I cannot
say. If it's being recognized as a Text field or a number you may need
to parse it into a Date/Time; for example,

CDate(Mid([ODate], 3, 2) & "/" & Left([ODate], 2) & "/" &
Right([ODate], 2))

will interpret the date as mm/dd/yy and (subject to the 00-29
convention above) return the correct date.

John W. Vinson[MVP]
 
D

don

It appears that what you said about the date:'Access by default treats
two-digit years in the following manner: 00 through 29 are in the current
(21st) century; 30 through 99 are in the 20th century, only holds if you
enter the date or create the date in access. If you are linking to an
external source such as an oracle table, the dates are not interpreted the
same way, we have dates that look like '840101' and they are coming out lik
2084/01/01'. I was hoping that access would allow us to put a mask on the
date field that would interpret years 30 - 99 as the 20th centrury.
--
Don


John Vinson said:
How do you display a date that has a two digit year, i.e 840902, so that it
comes out as 1984 and not 2084 (from an oracle linked table). In oracle you
can specify a mask of 'rr/mm/dd''. Is there a similiary date mask/format in
access??
Click to expand...

Access by default treats two-digit years in the following manner: 00
through 29 are in the current (21st) century; 30 through 99 are in the
20th century. Entering 8/13/84 will in fact be interpreted as 1984.

An Access Date/Time value is actually stored as a double float number:
a count of days since midnight, December 30, 1899. They can be
formatted however you like - see the Custom formats under Date in the
online help. You can use yymmdd (840902), mm/dd/yy (09/02/84),
mm/dd/yyyy (09/02/1984), or a wide variety of other formats. These
don't affect what's stored in the database, just how it's displayed.

Just how Access is interpreting your linked Oracle table date I cannot
say. If it's being recognized as a Text field or a number you may need
to parse it into a Date/Time; for example,

CDate(Mid([ODate], 3, 2) & "/" & Left([ODate], 2) & "/" &
Right([ODate], 2))

will interpret the date as mm/dd/yy and (subject to the 00-29
convention above) return the correct date.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

It appears that what you said about the date:'Access by default treats
two-digit years in the following manner: 00 through 29 are in the current
(21st) century; 30 through 99 are in the 20th century, only holds if you
enter the date or create the date in access. If you are linking to an
external source such as an oracle table, the dates are not interpreted the
same way, we have dates that look like '840101' and they are coming out lik
2084/01/01'. I was hoping that access would allow us to put a mask on the
date field that would interpret years 30 - 99 as the 20th centrury.
Click to expand...

It's not a matter of "a mask". I SUSPECT - without good evidence -
that the Oracle value is in fact some sort of a date/time value which
may in fact contain the 2084 date, in a way which is concealed from
view. If you look at the field in table design view, what are its
properties?

John W. Vinson[MVP]
 
D

don

the properties are date time...in oracle to get the year to display properly
you have to use mask such as 'rr/mm/dd' instead of 'yy/mm/dd'. Is there any
such equivalent in access?? What did people do that had linked tables with
old dates prior to 1999 that were stored in a 'yy/mm/dd/' format?
 
J

John Vinson

the properties are date time...in oracle to get the year to display properly
you have to use mask such as 'rr/mm/dd' instead of 'yy/mm/dd'. Is there any
such equivalent in access?? What did people do that had linked tables with
old dates prior to 1999 that were stored in a 'yy/mm/dd/' format?
Click to expand...

Access' closest equivalent is that you can use yy to display a
two-digit year, yyyy to display a four-digit year. What do you see if
you set the Format of this field to yyyymmdd, or mm/dd/yyyy?

I'm sorry, I haven't worked with Oracle in several years and a) am not
familiar with the rr/ date format, and b) not familiar with how Oracle
now handles dates. I'd suggest you repost with a different subject
line; I'll lay back and let someone with more Oracle experience reply.

John W. Vinson[MVP]
 
Top