Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007

    Unanswered: Changing Date format

    How do I change the date format from 2007 to 2008 in a text field

    Currently the format is “20081231” – I would like to change the first 4 characters to “2007” – so I’ll end up with “20071231”

    I’m using Access to do this, the table I’m using is link table (with a globe picture)

    Hope someone can help, many thanks

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59 you mean the format or the actual data?
    ..incidentally this is yeat another good reason not to store dates as text... store 'em in the natural datatype which is date/time

    assuming you want to change the data Id try an update query

    something like
    update <mytablename>
    set <mydatecolumn> = "2007" & right(<mydatecolumn>, strlen(<mydatecolumn>-4))
    where  <mydatecolumn> like "2008*";
    effectively that query reads
    update the specified column (<mydatecolumn>) to....
    2007 and append the reaminder of the existing datecolumn.
    the reason for the right(<mydatecolumn>, strlen(<mydatecolumn>)-4)
    is to cater for the possibility that the date might not be 8 characters.
    the strlen returns the number of characters in the datecolumn, the right bit
    . the reason we subtract 4 is that we are using the first 4 characters for the year so we don't want these to repeat.

    note this will not update any date values which are using 2 digit years unless you have some records for July 2000...

    however Id want to do that on a local copy of the data at first top check the query is correct...... and I haven't tested the query

    once I was happy that the query was updating the records I wanted and only those then I'd be happy to run it against the remote data.... but be very very careful, just in case you screw the data.

Posting Permissions

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