Results 1 to 4 of 4

Thread: Format Date

  1. #1
    Join Date
    Feb 2004
    Posts
    214

    Talking Unanswered: Format Date

    I have a file that I import into my db. The date comes across as 1070710 for 07/10/2007. The 107/ refers to the year, the next /07 is the month, and the last /10 is the day.

    How can I format this date to show 7/10/2007?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Can you build it from a string? Assuming this is the format it always comes in, do like...
    Code:
    Dim dateX As Date, strX As String
    strX = "1070710"
    dateX = CDate(Mid(strX, 4, 2) & "/" & Right(strX, 2) & "/" & Mid(strX, 2, 2))
    This is assuming the data always comes in as you set out in your rules.

    EDIT:
    I'm not sure how you're date will work when you get to the year 2100 or for any date back in the 20th century...
    Me.Geek = True

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    To carry it further, if "107" is the year, it's counting from 12/31/1899. 1/1/1900 would come across as "0000101". Ergo, let's extend Nick's code:

    Code:
    Dim dateX As Date, strX As String, lYear As Long
    strX = "1070710"
    lYear = Val(Left(strX,3)) + 1900
    dateX = CDate(Mid(strX, 4, 2) & "/" & Right(strX, 2) & "/" & lYear)
    Sam

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    And, it probably wouldn't hurt to import the data into a temporary file first, then run a table update query to copy/convert the data to appropriate types in the target table.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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