Results 1 to 4 of 4

Thread: Date (2000)

  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date (2000)

    I have a table full of dates in this format 19920129 It is also in a text field. How do I get this formatted to be a date/time field in 01/20/1992????

    Thanks!!!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Date (2000)

    You will need to add an additional field (date type field) to your table and using an update query set this date field equal to DateSerial(val(Mid(OldDateField,1,4)),val(Mid(OldD ateField,7,2)),val(Mid(OldDateField,5,2)))

    I am assuming that you mean either the 20th not the 29th.

    After checking your update has worked just delete the old date field from your table unless you want to hold it for posterity (which isn't a bad idea, to hold it I mean)
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Date (2000)

    Pat

    Shouldn't this be
    DateSerial(val(Mid(OldDateField,1,4)),val(Mid(OldD ateField,5,2)),val(Mid(OldDateField,7,2)))

    Dateserial requires year, month, day in that order
    Regards
    John



  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Date (2000)

    You are quite right John.
    However, I assumed that the date in the text field was yyyyddmm (19920129).
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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