Results 1 to 3 of 3
  1. #1
    Hobbes29 is offline Novice
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Posts
    10

    Split string into Date and Time Columns?

    I am looking for assistance in a sql query in MS Access 2007.



    I have imported a table into access. One of the columns is a column called DTG_LOCAL (which is short for Date/time group local time). This column is a string column with the format DDMMMYYYY:HH:MM:SS or 01JAN2010:13:40:00.

    I am looking for a sql query that will take the string DTG_LOCAL column and break out DDMMMYYYY and HH:MM to their own columns with an additional column with just HH for a total of three new columns with the broken out information. The three columns would be DATE, TIME, and HOUR; Lastly, convert the columns from a text column to DATE/TIME respectively.

    DTG_LOCAL --> DATE / TIME / HOUR or
    01JAN2010:13:40:00 --> 01JAN2010 / 13:40:00 / 13

    The other columns in the file are not deleted.

    The table name is GPS_AI_Import.

    Thank you for any assistance,

    Ken

  2. #2
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    I don't recommend you label your fields/columns with "Date", "Time", or "Hour" as these are reserved words in Access.

    Code:
    SELECT GPS_AI_Import.DTG_LOCAL, DateValue(Mid([DTG_LOCAL],6,4) & " " & Mid([DTG_LOCAL],3,3) & " " & Mid([DTG_LOCAL],1,2)) AS DateExtract, TimeValue(Mid([DTG_LOCAL],11)) AS TimeExtract, Hour([TimeExtract]) AS HourExtract
    FROM GPS_AI_Import;
    Cheers,

  3. #3
    Hobbes29 is offline Novice
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Posts
    10
    ConneXionLost,

    Thank you. This did exactly what I needed it to do. I will change the column headers from the reserved words to non-reserved words.

    Ken
    Last edited by Hobbes29; 06-08-2010 at 06:51 PM. Reason: spelling

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

Similar Threads

  1. Hiding columns in split form.
    By cementblocks in forum Forms
    Replies: 5
    Last Post: 03-11-2014, 07:16 AM
  2. Help with date stored as string
    By weisslakeguy in forum Queries
    Replies: 8
    Last Post: 05-26-2010, 11:14 AM
  3. Replies: 4
    Last Post: 01-15-2010, 06:06 AM
  4. Convert string to date
    By ~SwAmPdOnKeY~ in forum Queries
    Replies: 4
    Last Post: 09-11-2008, 07:19 PM
  5. split a column into two seperate columns
    By nybanshee in forum Access
    Replies: 2
    Last Post: 08-14-2008, 04:52 PM

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