Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change Date Format (2003 (2000 format))

    I'm putting together a reporting system from AS400 ODBC link (& pass-through queries), but the AS400 date format is a 7 digit number; years since 1900 i.e. 105 = 2005, month i.e. 01 = Jan, day i.e. 08 = 8th.

    Now I have to make every part of the DB work at it's optimal speed & I'm not sure when, where & how would be the best way of converting to a standard dd/mm/yyyy format.

    Help please.
    TIA
    EnB

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

    Re: Change Date Format (2003 (2000 format))

    You could calculate the date in a query, but that will probably have to be an Access query, so performance might suffer. The expression to get a date from the number field would be
    =DateSerial(1900+Left([FieldName],3),Mid([FieldName],4,2),Right([FieldName],2))

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change Date Format (2003 (2000 format))

    There is another way to do this but it involves creating a table in the Access database that contains an AS400 date format field containing the entire 7-digit date as the primary key. It would also contain a field with the equivalent standard date as a datetime field and a unique key. Other fields would contain the year, the year number since 1900, the month number and day number and any other calculated values you need like quarter or whatever. The structure might look like this:


    <table border=1><td>AS400Date</td><td>DateKey</td><td>YearValue</td><td>MonthInYear</td><td>DayInMonth</td><td>DayInYear</td><td>YearNumber</td><td>1050108</td><td>01/08/2005</td><td>2005</td><td>1</td><td>8</td><td>8</td><td>105</td></table>
    Then you could simply join this table to the date field of the AS400 table to get an instant conversion to the Access date without doing any calculations at all. The calculations are done when the records in the datetime table are created for the dates you will be using. This is the method data warehousing applications use, and it is exceptionally fast.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Date Format (2003 (2000 format))

    Thanks for the replies, I particularly like Charlottes' solution, it has a simplicity & elegance I can appreciate.

    Thanks again.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change Date Format (2003 (2000 format))

    Another advantage of that technique is that it makes it very easy to check dates within, say, the last 6 months, because you can do any necessary date math on the Access date or you can do things like filtering for all transactions between June 2004 and January 2005 by using the YearValue and MonthInYear fields without doing any date math at all.
    Charlotte

Posting Permissions

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