Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date conversion (2002)

    I have date data that was imported as text in several large tables. I'd like to run an update statement to update a new date data type field in the tables for all the rows using the text based data. The current text data is in YYMMDD format, I'd like to convert it to a short date type "MM/DD/YYYY". I pasted my update query below, when I run it it says that it can't complete the update because of data type conversion.

    UPDATE tblSalesTO
    SET tblSalesTO.Dt_Sale1 = "#" & Mid(Dt_Sale,3,2) & "/" & Mid(Dt_Sale,4,2) & "/" & Mid(Dt_Sale,1,2) & "#"


    Sample data: 960603


    Thanks in advance for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date conversion (2002)

    You should use Mid(Dt_Sale,<big>5</big>,2) instead of Mid)Dt_Sale,<big>4</big>,2). If that does not help, try using the DateValue function:

    SET tblSalesTO.Dt_Sale1 = DateValue(Mid(Dt_Sale,3,2) & "/" & Mid(Dt_Sale,5,2) & "/" & Mid(Dt_Sale,1,2))

Posting Permissions

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