Results 1 to 4 of 4
  1. #1
    Ant_Snell is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    34

    Importing CSV Time stamp as date format


    I am importing data from a CSV file, it contains a ‘Start Time’ field expressed in the following format: 2021-04-09 12:30:00 +0100. I want to import the field as a Time/Date format so that I can calculate on it. If I change the Short Text to Date & Time during the import it errors. If I leave the format as short text can I then convert to a Date/Time format using a built in function with the function builder or any other way. Thanks for any help

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,300
    Could use expression in query: CDate(Left([fieldname],19))

    Null field will show #Error
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,823
    Here's a function that will return a date from the string. It strips characters off the right side beginning one space before the "+".

    Code:
    Public Function convdate(sDate As String) As Date
        convdate = Left(sDate, InStr(sDate, "+") - 1)
    End Function
    In Immediate window:
    ?convdate("2021-04-09 12:30:00 +0100")
    4/9/2021 12:30:00 PM

  4. #4
    Ant_Snell is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    34
    That's sorted it! Thanks all.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Time/Date stamp Location
    By Western_Neil in forum Database Design
    Replies: 6
    Last Post: 07-10-2016, 09:29 AM
  2. Date and Time Stamp Question
    By NightWalker in forum Programming
    Replies: 12
    Last Post: 06-21-2016, 06:29 AM
  3. Stamp date, time and user?
    By ricanos in forum Modules
    Replies: 1
    Last Post: 04-06-2016, 08:41 AM
  4. Replies: 1
    Last Post: 04-01-2015, 01:29 AM
  5. Date and Time Stamp
    By zoooza84 in forum Access
    Replies: 2
    Last Post: 08-06-2011, 04:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums