Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Unanswered: date format in yyymmdd with no time in export

    First my apologies, I am very novice in Microsoft Access (2003) and hope my questions are appropriate for this forum.

    I have built an Access query that contains DOB. The date comes in from my client’ Cache database in the format 06/24/1924. I need it to display in an export file in the format ‘yyyymmdd’. I was able to accomplish this in the query by using the FORMAT in properties area.

    Problem was when exporting query to a .csv the date displayed in 19240624 00:00:00.

    I then used an expression DOB: DateValue([date_of_birth]) and reran the query. The date display in the correct format on the query but when exporting to a .csv it now display as 06/24/1924 00:00:00.

    I then tried Right(date_of_birth,9). This resulted in the format in the time being removed in both the query and the export but would not honor the format. The query showed 06/24/1924 and the export showed 06/24/1924 with no time.

    Can anyone give me guidance on how to format the date so it exports to a ‘yyyymmdd’ format without the time.

    Thanks in advance for any help you can give.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you can set the values as part of the export format to say how date/time values should be exported

    ...failing that
    have a look at the format function
    use it in your export query as format(<myvalue>,<myformatmask>)

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    try this

    format(date(),"yyyy/mm/dd")
    returns
    2007/10/25
    Dale Houston, TX

  4. #4
    Join Date
    Sep 2007
    Posts
    18
    Thanks Healdem for the reply.

    I will try to explain again as I fear I was not clear. I have used yyyymmdd in the Format function (YYYYMMDD) but that didn't get rid of the time. so I used the RIGHT(date,9) in the expression builder. the changed the format back to mm/dd/yyyy but fotunately did take away the zero time. So I thought they same as you, use the advanced unctions to to reformat. In the advanced section of the export I selected YMD with 4 year format, leading zeros in date with no '/' in the date.
    It still exports as 06/24/1924

  5. #5
    Join Date
    Sep 2007
    Posts
    18
    Thanks Dale

    I tried datevalue([Date_of_birth],"yyyymmdd" and got what I needed.
    I truly appreciate everyone efforts in help me gain more knowledge on this product. It is a far cry from what I am used to doing. I am an assembler/fortran programmer and don't have much of a chance to use a much more user friendly product. I think I am going to enjoy using Access.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You need to convert the date to a string using CStr. I have had to deal with the problem you are dealing with. Access tends to ignore formatting with numbers when exporting data to a file and the only way to get the format you want is to force it to a string.

    Try CStr(Format(Date(),"yyyymmdd"))

  7. #7
    Join Date
    Oct 2012
    Posts
    1
    Thanks AXSPROG (Dale), I used this code and it works.

    Just tweeked it to what I needed:
    =Format(Date(),"yyyy-mm")

Posting Permissions

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