1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access - Convert Date Time to Date

Discussion in 'Business Applications' started by chudok01, Feb 16, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    190
    I have a Date/Time field that I need converted to just a Time field.

    It comes back with 2/11/12 0:00:00

    I need just the 2/11/12

    I thought about changing it to a text field but I think there will be an issue with the month because it does not bring back a 0 in from the months with only 1 digit.

    This table that I am creating is being sent to a txt file and that is where I see the issue. It looks good in Access, but not the txt file.

    Any suggestions?
     
  2. Sponsor

  3. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Do you really need to convert it since the time is already a part of the field? You can separate out any part of a date/time field using functions just for display and querying purposes.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,617
    As Rockn says in a query, Report or Form you can use the "Format" function to display the date in any way that you want.
    You can also use functions in a query to separate out the various parts of the date like day, month, year time etc.
     
  5. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    190
    The issue is that I am exporting this file in either a csv or txt for our ERP system and I either need it to be a text field that only looks like a date (not date and time) or a date field that only has a date.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,617
    If you set the format to short date in the query and export the query what do you get?
     
  7. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    190
    Still the long date. It views the short date but puts the long date.

    This is what I have going on. I have a query that grabs data from another database and created table 1.
    I then query table 1 and a couple of other tables and create another table 2.
    Then I have query that created a table that exports. to either a csv or txt.

    So in any of the steps I have tried to change the date and it is not working. I am going to try to change to a text with the mask of ##/##/## and see if that works.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,617
    Are you using and Export Specification?
    You could of course build the Date in a separate query column using
    newdate: format([datefield], "dd") & "/" & format([datefield], "mm") & "/" format([datefield], "yyyy")
    Where datefield is the actual name of your date field.
     
  9. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    190
    Yes i am using the Export Specification. I remember I had issues with this prior and I can't remember how I fixed it.... I am going to have to research my past issue with the export specification. I can't even remember where to get to it...
     
  10. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    190
    I ended up using

    newdate: format([datefield], "dd") & "/" & format([datefield], "mm") & "/" format([datefield], "yyyy")

    worked!!

    Thank you!!!
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1041380