Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert String to Date

    I have the following string I need to convert to a date/time field via my Access query. Any suggestions?

    20110815 10:44:15

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You could try the CDate() function directly on the the string and see how it goes. Otherwise you may need to use the DateSerial() function, which would also require using the Left() or Mid() functions to select appropriate parts of the string.
    Wendell

  3. #3
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    AFAIK CDate() will not work with this format.

    Try:
    for the date part:
    Code:
    ? DateSerial(Left("20110815 10:44:15 ",4),Mid("20110815 10:44:15 ",5,2),Mid("20110815 10:44:15 ",7,2))
    8/15/2011

    Fir the time part:
    Code:
    ? TimeSerial(Mid("20110815 10:44:15 ",10,2),Mid("20110815 10:44:15 ",13,2),Mid("20110815 10:44:15 ",16,2))
    10:44:15 AM
    for both date and time add the above two together:

    Code:
    ? DateSerial(Left("20110815 10:44:15 ",4),Mid("20110815 10:44:15 ",5,2),Mid("20110815 10:44:15 ",7,2)) + TimeSerial(Mid("20110815 10:44:15 ",10,2),Mid("20110815 10:44:15 ",13,2),Mid("20110815 10:44:15 ",16,2))
    8/15/2011 10:44:15 AM
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Posting Permissions

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