Professional Web Applications Themes

Date conversions... - Microsoft Access

I'm creating a new database program, but I need to import some data from an old program (both Acc2000). Howerver, some of the dates stored in the old database are stored as text, not date/time, and when I convert them, I get really ugly dates, like 10/02/1931 instead of 10/31/2002. Is there any way to convert the dates properly, or am I going to have to change all these dates manually after I import them?...

  1. #1

    Default Date conversions...

    I'm creating a new database program, but I need to import
    some data from an old program (both Acc2000). Howerver,
    some of the dates stored in the old database are stored as
    text, not date/time, and when I convert them, I get really
    ugly dates, like 10/02/1931 instead of 10/31/2002. Is
    there any way to convert the dates properly, or am I going
    to have to change all these dates manually after I import
    them?
    Mark Guest

  2. #2

    Default Date conversions...

    Microsoft stores dates as a number. Each date is the
    number of days before of after the base of all dates which
    is the 31st December 1899 which equals 1. Today (7th July
    2003) is 37809. If you have data in a field that is not
    properly formatted as Date, Microsoft products cannot
    convert them to the number value.

    If you pump everything to Excel and then format the
    appropriate columns to date you might get away with then
    re-importing to Access with some success otherwise try to
    organise them in some way which places all of the badly
    entered date values together so that you have to only
    review those records to update manually and not all of
    your data.

    Bish
    >-----Original Message-----
    >I'm creating a new database program, but I need to import
    >some data from an old program (both Acc2000). Howerver,
    >some of the dates stored in the old database are stored
    as
    >text, not date/time, and when I convert them, I get
    really
    >ugly dates, like 10/02/1931 instead of 10/31/2002. Is
    >there any way to convert the dates properly, or am I
    going
    >to have to change all these dates manually after I import
    >them?
    >.
    >
    Bish Guest

  3. #3

    Default Re: Date conversions...

    In which way they are ugly???

    02 October 1931 and
    31 October 2002

    look to be perfectly valid to me.

    If you have 2-digit year, Windows makes some assumptions about the century
    and you have to adjust accordingly.


    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Mark" <mjkoopmanyahoocom> wrote in message
    news:77f101c3449e$21cab480$a401280aphx.gbl...
    > I'm creating a new database program, but I need to import
    > some data from an old program (both Acc2000). Howerver,
    > some of the dates stored in the old database are stored as
    > text, not date/time, and when I convert them, I get really
    > ugly dates, like 10/02/1931 instead of 10/31/2002. Is
    > there any way to convert the dates properly, or am I going
    > to have to change all these dates manually after I import
    > them?

    Van T. Dinh Guest

  4. #4

    Default Date conversions...

    Yes,

    You can convert any type of date into a valid date. You
    have to know a few string manipulation functions such as
    Mid(), Left(), Right etc and you can use the date
    conversion function cvdate().

    For example to convert "021231" (a string) into a valid
    date you would extract using mid() function each of the
    three pairs of digits "02", "12" and "31" and them compile
    them into a proper date like this cvdate("12" & "/" & "02"
    & "/" & "31") which will give you #12/31/02# or Dec 31,
    2002.

    This works perfectly fine on a machine that is using
    mm/dd/yy as the date format.

    M.
    >-----Original Message-----
    >I'm creating a new database program, but I need to import
    >some data from an old program (both Acc2000). Howerver,
    >some of the dates stored in the old database are stored
    as
    >text, not date/time, and when I convert them, I get
    really
    >ugly dates, like 10/02/1931 instead of 10/31/2002. Is
    >there any way to convert the dates properly, or am I
    going
    >to have to change all these dates manually after I import
    >them?
    >.
    >
    Milan Guest

Similar Threads

  1. Question PDF to PDF/A conversions
    By Unregistered in forum Brainstorming Area
    Replies: 0
    Last Post: January 31st, 08:11 PM
  2. Hebrew Calendar conversions?
    By Shmuel (Seymour J.) Metz in forum PERL Modules
    Replies: 4
    Last Post: April 21st, 07:36 PM
  3. batch conversions
    By zimmy6911 in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 9
    Last Post: April 26th, 06:07 PM
  4. Case conversions
    By James Edward Gray II in forum PERL Beginners
    Replies: 8
    Last Post: August 15th, 10:10 PM
  5. ASP/SQLServer character conversions
    By Félix López in forum ASP Database
    Replies: 1
    Last Post: July 21st, 08:20 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139