Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Converting date format

Discussion in 'Information Technology' started by rockocubs, Nov 3, 2005.

  1. rockocubs

    rockocubs
    Expand Collapse
    Guest

    I have imported a text file to an access table i have some date fields
    in yyyymmdd format so like this 20051006 , I imported everything as a
    text field in the table. i want to apend to another table but into
    date format fields, i want to get to a format of mm/dd/yy format. Is
    there an easy way to do this. I have tried just the append to a
    time/date field with no format for starters and the date come in as
    null.Oh this access 2000.
     
  2. Loading...

    Similar Threads Forum Date
    Jehovah's Witnesses Are Out And Converting! Blogs Jul 31, 2016
    Basic Questions about Converting to Sikhism Questions and Answers Nov 15, 2013
    Converting to Sikhism Hard Talk Aug 31, 2012
    Why are people converting to Sikhism? Sikh Sikhi Sikhism Jul 1, 2012
    Question About Converting To Sikhism For A Married Person Questions and Answers Jun 3, 2012

  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Use:

    DateSerial( Left([DateStr], 4), Mid([DateStr], 5, 2), Right([DateStr],
    2) )

    to convert your date string to a date value. You can then format the date
    value to whatever format you need.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "rockocubs" <rphipps@indy.rr-dot-com.no-spam.invalid> wrote in message
    news:7ZydnbXbf9Y_9vTeRVn_vA@giganews.com...
    >I have imported a text file to an access table i have some date fields
    > in yyyymmdd format so like this 20051006 , I imported everything as a
    > text field in the table. i want to apend to another table but into
    > date format fields, i want to get to a format of mm/dd/yy format. Is
    > there an easy way to do this. I have tried just the append to a
    > time/date field with no format for starters and the date come in as
    > null.Oh this access 2000.
    >
     
  4. rockocubs

    rockocubs
    Expand Collapse
    Guest

    confused

    Not sure where to put the code you entered at?
     
  5. rockocubs

    rockocubs
    Expand Collapse
    Guest

    date

    I got it to work with access 97 at work, so will try it when i get
    home on my access 2000. Thanks for the help.
     
  6. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Re: confused

    In your Append Query like:

    INSERT INTO DestTable ( ..., DateField, ... )
    SELECT ..., DateSerial( Left([DateStr], 4), Mid([DateStr], 5, 2),
    Right([DateStr], 2) ), ...
    FROM SourceTable

    Check Access Help on Append Queries.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "rockocubs" <rphipps@indy.rr-dot-com.no-spam.invalid> wrote in message
    news:jIadnfBRZfL_iffeRVn_vA@giganews.com...
    > Not sure where to put the code you entered at?
    >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 02 Nov 2005 19:45:06 -0600,
    rphipps@indy.rr-dot-com.no-spam.invalid (rockocubs) wrote:

    >I have imported a text file to an access table i have some date fields
    >in yyyymmdd format so like this 20051006 , I imported everything as a
    >text field in the table. i want to apend to another table but into
    >date format fields, i want to get to a format of mm/dd/yy format. Is
    >there an easy way to do this. I have tried just the append to a
    >time/date field with no format for starters and the date come in as
    >null.Oh this access 2000.


    In your Append query, use a calculated field:

    DateSerial(Left([textdate], 4), Mid([textdate], 5, 2),
    Right([textdate], 2))

    and append this to the Date/Time field. You can use the same
    expression in an Update query if the records already exist in your
    target table.

    John W. Vinson[MVP]
     

Share This Page