Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61

    Unanswered: Problems with changing text to date

    Ive got text that is supposed to be dates, but if i try to change them to dates. None of them will be changed and I get an error log.

    The dates are in the format 19960803(last order date) in table SUPMARK .

    Changing them by hand is to much, because there are almost 50.000

    Does anyone know how i can do this another way?

  2. #2
    Join Date
    Jul 2002
    Location
    Eastbourne, UK
    Posts
    9

    Smile

    Dunno if you use American or British date format, but here is a simple solution for a British format date:


    Function TextToDate(TextIn) As Date
    Dim yr As String
    Dim mn As String
    Dim dy As String
    Dim tmp As String
    yr = Left$(TextIn, 4)
    mn = Mid$(TextIn, 5, 2)
    dy = Mid$(TextIn, 7, 2)
    tmp = dy & "/" & mn & "/" & yr
    TextToDate = CDate(tmp)
    End Function

    I tried this with the code:

    MsgBox "Date is " & TextToDate("19960803")

    and got: "Date is 03/08/1996"

    Hope this helps.

  3. #3
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61
    How can i get those Dates in a Table?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    insert into newtable
    (foo, datetimefield)
    select 'bar'
    , Left(textdate,4)
    & '-' & Mid(textdate,5,2)
    & '-' & Mid(textdate,7,2)
    from yourtable


    rudy

  5. #5
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61

    Smile

    Thx for the help everyone

Posting Permissions

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