Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

displaying dates from a linked oracle table

Discussion in 'Information Technology' started by don, Nov 19, 2005.

  1. don

    don
    Expand Collapse
    Guest

    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??
    --
    Don
     
  2. Loading...

    Similar Threads Forum Date
    Heritage Canadian art gallery displaying Sikh Maharaja Ranjit Singh’s throne History of Sikhism Nov 27, 2010
    Sikh News BREAKING: Young Sikh Shot Dead In Jalalabad, Afghanistan . - Sikh24 News & Updates Breaking News Oct 2, 2016
    Pacific Oil slicks spotted in search for missing Malaysia Airlines plane (Live updates) Breaking News Mar 8, 2014
    India Candidates flock to Dera Sacha Sauda Breaking News Jan 17, 2012
    India Seven SAD Candidates of SGPC Win Unopposed Breaking News Aug 27, 2011

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 18 Nov 2005 08:09:30 -0800, don
    <don@discussions.microsoft.com> wrote:

    >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??


    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]
     
  4. don

    don
    Expand Collapse
    Guest

    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" wrote:

    > On Fri, 18 Nov 2005 08:09:30 -0800, don
    > <don@discussions.microsoft.com> wrote:
    >
    > >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??

    >
    > 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]
    >
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 18 Nov 2005 12:49:11 -0800, don
    <don@discussions.microsoft.com> wrote:

    >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.


    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]
     
  6. don

    don
    Expand Collapse
    Guest

    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?
    --
    Don


    "John Vinson" wrote:

    > On Fri, 18 Nov 2005 12:49:11 -0800, don
    > <don@discussions.microsoft.com> wrote:
    >
    > >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.

    >
    > 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]
    >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 18 Nov 2005 13:44:08 -0800, don
    <don@discussions.microsoft.com> wrote:

    >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?


    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]
     

Share This Page